Are you using the Azure benefits in your MSDN subscription? Like an Aston Martin?

Most developers and DBAs that I come across have some level of access to an MSDN subscription, yet I am surprised about how many have not yet activated the Azure benefits that are associated with those subscriptions.

If you want to stay relevant within your organisations, it's important to get your head around Azure and the best part is that you can do much of it at no additional cost.

All you need to do is to activate your benefits. You'll find details here:

http://bit.ly/140upRt

And at the moment, there's a chance to win an Aston Martin just for doing so. James Bond fans could be "shaken and stirred" 🙂

Listeners: the missing link for SQL Server Availability Groups in Azure VMs

One of the topics that I'm covering in one of my sessions this year at TechEd Australia is around SQL Database and SQL Server in Azure VMs for DBAs. In that session, I'm planning to spend a while on hybrid on-premises / cloud scenarios.

Availability groups were introduced in SQL Server 2012 and while it's unlikely that you'd use a synchronous replica in an Azure VM (unless the primary was also in an Azure VM), hosting an asynchronous replica in an Azure VM makes a lot of sense for disaster recovery scenarios where latency isn't an issue.

Availability group listeners provide a method for client applications to be transparently redirected during failover occurrences. The challenge with hosting an availability group replica in an Azure VM has been that an availability group listener required an additional IP address which was not available. So, previously, you could only configure SQL Server in an Azure VM as a database mirroring type of replica.

Amongst a raft of other great enhancements in the announcement from Scott Guthrie today is really welcome news that the missing link for this scenario has now been provided. Availability group listeners are fully supported when using SQL Server hosted in an Azure VM. This is great news.

Report Manager: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel

I had seen this issue before but found another cause for it today.

In this case, the client was using SCCM (System Center Configuration Manager) and couldn't access reports from the reporting server. After resolving some other issues, I was left with "The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel" when I tried to go to http://servername/reports.

When I've seen this previously, it's been to do with the trust relationship between a pair of servers. However, I hadn't seen it come up before on a single server.

When I checked the SSL bindings in Reporting Services Configuration Manager, in the Advanced tab, I found that no certificate was associated with the SSL configuration. When I tried to edit it, and choose the cert from the dropdown list, the certificate binding could not be applied. (It would be useful if it told you why at that point). That had me puzzled until I opened Report Manager in IE and viewed the certificate. The certificate had been generated onsite and had expired.

Hope this helps someone. (Or helps me the next time I see it and can't recall what it was :-))

Do you find T-SQL scripts hard to read with all the square brackets?

The T-SQL scripting tools (such as SQL Server Management Studio) provide two options for scripting names: either to quote them or to omit quotes.

If you avoid things like spaces in object names, you can mostly get away without quoting i.e.

Sales.Customers is just fine and doesn't need to be [Sales].[Customers].

Even then, one problem that can arise is that a name you had used in your code could become a SQL reserved word. In that case, you either need to change every reference to it (painful), or quote it wherever it's used (also painful). So quoting by default is always a safe option. However, it makes the scripts much harder to read due to visual noise.

What I feel is really needed is some way to determine if a name needs quoting i.e. a function such as:

ISVALIDASOBJECTNAME()

So for example:

ISVALIDASOBJECTNAME('Sales') would return 1 but ISVALIDASOBJECTNAME('Sales Targets') would return 0.

This functionality could then be used to extend the QUOTENAME function with an optional parameter that says:

QUOTENAME('Sales Targets',0) for "always quote" or QUOTENAME('Sales Targets',1) for "quote only if needed). They could leave 0 as the default so it is option and the function still works unchanged when no parameter is supplied.

That would then make it easy for the team to change SSMS to have a single setting for how you'd like scripts generated.

If you agree with this, please vote here: https://connect.microsoft.com/SQLServer/feedback/details/796172/isvalidasobjectname-and-quotename-enhancement-to-clean-up-script-readability

 

Performance tuning of tabular data models in Analysis Services

More and more practical information around working with tabular data models is starting to appear as more and more sites get deployed.

At SQL Down Under, we've already helped quite a few customers move to tabular data models in Analysis Services and have started to collect quite a bit of information on what works well (and what doesn't) in terms of performance of these models. We've also been running a lot of training on tabular data models.

It was great to see a whitepaper on the performance of these models released today.

Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services was written by John Sirmon, Greg Galloway, Cindy Gross and Karan Gulati. You'll find it here:

 http://msdn.microsoft.com/en-us/library/dn393915.aspx