SQL Client Aliases - Follow-up

Well, after much debate, we've decided to ditch SQL Client Aliases and go with DNS CName records instead. This will mean assigning separate IP addresses to each instance on our multi-instance servers and changing every non-standard port in the enterprise back to 1433, but we feel it's ultimately worth the effort. We're even going as far as setting up a separate DNS zone for our SQL Servers and adding that as a DNS search suffix enterprise-wide such that we DBAs can manage the CNames ourselves rather than relying on the 24-hour SLA of the infrastructure team to get anything done for us :) That said, I'm still kind proud of the Client Alias solution I developed, so I'll still be polishing it up and posting it here, though likely not for a few weeks as I'm taking a couple of weeks off to recharge.

SQL Client Aliases: Pros, Cons, How to Manage Them + One Big Gotcha

One pain point here in our organization is moving databases from one server and/or instance to another. There are two items, specifically, that are most painful.

First, since many of our applications are developed in-house, and the environment-specific configurations are source-controlled and are part of the application deployment, changing a connection string means re-deploying the application and taking an outage of longer duration than would be otherwise.

Second, many third-party applications make changing connection strings phenomenally difficult. Anyone who has tried to move Sharepoint databases can relate this this pain-point.

We're looking at having to do a production hardware and platform refresh over the coming months, including moving to VMWare for our production SQL Servers. Since every database in the organization is going to be moving, we decided to solve this problem once and for all by abstracting the SQL Server Host and Instance details from the applications. In a SQL Server-only environment, there are really two ways to do this. The first is using DNS CNAME entries, the second is to use SQL Client Aliases. There are pros and cons to each.

DNS CNAME: Essentially 'aka' entries in DNS. You map the CNAME to the actual hostname in DNS and then refer to the CNAME in your connection string. If the database moves, simply change the associated hostname and clients will redirect.

Pros:

• Central management. Make the change once and it's changed for the entire enterprise.
• Works on all SQL Server Clients

Cons:

• No support for named instances or non-standard ports(non-1433). If you use non-standard ports or named instances, such as in a multi-instance Windows Failover Clustering setup, you must still hardcode the instance name and/or port in the connection string. 
• Due to DNS caching, zone transfer timing, etc, you may not have complete control over when a changed hostname in the CNAME will be picked up and used by all clients.
• A Domain (or DNS) admin is necessary to make the change for you.
• No knowledge of all affected systems as the CNAME is 'global'.

SQL Client Aliases: Acts kind of like a 'hosts' file for SQL Server, providing SQL Server-specific name translation for the local machine. 

Pros:

• Support for named instances and non-standard ports.
• No latency issues from zone transfers or DNS caching. Change is immediately effective once made.
• Server Admin or DBA can create and manage as necessary.
• Knowledge of all affected systems as the Alias is 'local'.

Cons:

• No central management. If a database moves, you need to know where all the aliases live and update them. 
• Difficult to change all aliases at precisely the same time.
• Works only with the Microsoft SQL Client. (no JDBC, even with MS drivers)

We'll come back to that last Con for SQL Client Aliases in a second...

We settled on using SQL Client Aliases, primarily for the support for named instances and non-standard ports. The big Con, though, for us, was no centralized management, so we rolled our own. I created a database to track application-to-alias-to-SQLServer mapping and a set of powershell scripts to ease the deployment of Aliases. The scripts would take a target SQL Instance or AliasName as a parameter, read the details from the database and create or update all the affected aliases as appropriate. I plan to blog about this solution in the near future after we've worked the kinks out of it a bit. It was remarkably easy to do, since Aliases are just registry entries. 

This all worked great until we got to our first Java applications, that happened to be our main business application suite. Turns out SQL Client Aliases only work with the SQL Client, not JDBC. You'd think I'd have realized this given the name 'SQL Client Alias', but somehow that escaped me until this morning, after I was about 100 hours of development into this project. 

At this point I'm not sure what we'll do. We've determined that only about 5% of our total application inventory uses JDBC, so the problem is contained, but the affected apps are very high priority and have complex development cycles (5 QA environments + 2 shared integration environments + staging + production, etc). Perhaps we'll go ahead and use CNAMEs for that 5% and continue to use SQL Client Aliases for the remainder or perhaps we'll go entirely CNAME, I'm just not yet sure.

Slide Deck for my talk on ETL Tuning at SQLSaturday #287 in Madison

I'll be speaking at SQL Saturday #287 in Madison, WI this coming Saturday, March 28th. Here is a link to the slide deck and scripts I'll be using. Hope to see you there!

Slide deck from my "Performance Tuning ETL Operations" talk

Here is a link to download my slide deck from my recent talk at FoxPASS . I expect to have some detailed followup posts on topics covered in the talk as well as additional posts around SSDT in the coming weeks. Thanks!

Powershell SQL Agent Job Steps and the .Count Property

This has been frustrating me for a few days now and I finally cracked it, more or less.

I have a handful of SQL Agent jobs that run on our Central Management Server Instance. These jobs check certain settings across our environment and email me a report if any servers are out of compliance. I realize I could use Policy Based Management, but I've found this system works well, except for one thing...

The scripts populate an array in Powershell with the offending records. I then check the size of the array and if it has > 0 members, I email the results to an operator on the CMS instance. I realized recently that if the array has 1 member, I would not be notified. Only if there were two or more items in the array would I get an email. I had tested my script locally and it worked fine, the problem only seemed to occur when the script ran under the SQL Agent. Enter Google. Some searching turned up this article which describes how in versions of Powershell prior to v3 (the SQLPS.exe used by the SQL Agent is based on v2 in SQL 2008R2), single element (or empty) arrays are collapsed into a single (non-array) item, which lacks the .Count property. the workaround is to wrap the results as an array type with @(), like IF (@(QueryResults).Count -gt 0). Sure enough, this worked ... in testing on my local machine. Now, when running through the SQL Agent, I receive the emailed results even if the array is empty. Some testing confirmed that when the array is ostensibly empty, the Count property is still returning a value of 1, but only when running through the SQL Agent. WTH.

Having not yet discovered the underlying cause of this issue, I've found a way around it. I first convert the entire array to a string and test its length rather than the number of elements in the array. If the length > 1 then I know I should send the notification email. It's kind of a hack and I don't like it. So, if anyone reading this should know the real reason for the behavior and a better way around it, I'd love to hear it.

Should you find it useful, here is an example of the full script that I use to find databases not owned by 'sa'. You can add exceptions to it, to ignore specific non 'sa' users. This is useful to skip databases for applications like Sharepoint that use database ownership as part of its security strategy.

$ServerGroup = 'Production' #Replace with the name of your CMS Server group to check

$CMServer = 'CMSServerInstance' #Replace with the name of your CMS SQL instance

$Operator = 'DBAs' #Replace with the Operator Group that should be emailed

$FromAddress = 'sql_checkup@yourdomain.com' #Replace with From Address for email being sent

$msg_Subject = "$ServerGroup Database Ownership Report" #Replace with subject line for emailbeing sent

$SmtpServer = 'relay.yourdomain.local' #Replace with name of SMTP Server for email relay

## Fetch List of Servers in the group $ServerGroup from Central Management Server $CMServer ##

$ServerList = Invoke-Sqlcmd -Query "SELECT DISTINCT s.server_name AS 'ServerName'

FROM msdb.dbo.sysmanagement_shared_server_groups_internal g

INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal s

ON g.server_group_id = s.server_group_id

WHERE g.name = '$ServerGroup'

ORDER BY s.server_name ;" -ServerInstance $CMServer

## Build results by executing our query against each server in the list we just built ##

$QueryResults = foreach ($ServerName in $ServerList)

{

Invoke-Sqlcmd -Query "SELECT @@ServerName As ServerName, d.name As DatabaseName, s.name As Owner

       FROM sys.databases d LEFT OUTER JOIN sys.syslogins s on d.owner_sid = s.sid

       WHERE ((s.name <> 'sa' OR s.name IS NULL) AND ISNULL(s.name,'') NOT IN('DOMAIN\excluded_user', 'APP_ExcludedUser')) AND d.source_database_id IS NULL

       ORDER BY @@ServerName, d.name

" -ServerInstance $ServerName.ServerName

}

## If there are any rows in the result set, email it to the Operator specified in $Operator ##

$QueryResultsString = Out-String -InputObject $QueryResults

If ($QueryResultsString.Length -gt 0)

{

  ## Build data set of Email addresses for the operator specified (if more than one address, assumes ';' delimiter) ##

  $email_addresses = Invoke-Sqlcmd -Query "create table #email (address nvarchar(100))

  declare @email_addresses nvarchar(100)

  declare @address nvarchar(100)

  declare @Pos INT

  select @email_addresses = email_address from msdb.dbo.sysoperators where name = '$Operator'

  SET @email_addresses = LTRIM(RTRIM(@email_addresses))+ ';'

  SET @Pos = CHARINDEX(';', @email_addresses, 1)

  WHILE @Pos > 0

      BEGIN

          SET @address = LTRIM(RTRIM(LEFT(@email_addresses, @Pos - 1)))

          IF @address <> ''

              BEGIN

                  INSERT INTO #email (address)

                    VALUES (CAST(@address AS VARCHAR(MAX)))

              END

          SET @email_addresses = RIGHT(@email_addresses, LEN(@email_addresses) - @Pos)

          SET @Pos = CHARINDEX(';', @email_addresses, 1)

      END

  select * from #email

  drop table #email ;" -ServerInstance $CMServer

  ## Convert the data set to a string array for later use to build addressee's ##

  $email_addresses_array = @()

  for ($i=0; $i -le $email_addresses.Length; $i++)

    {

      $email_addresses_array = $email_addresses_array + ($email_addresses[$i].ItemArray -join ",")

    }

  ## Build the Email  and sent it##

  $Body = "The following databases are not owned by sa :`n"

  $Body += $QueryResults | Out-String

  $msg = New-Object System.Net.Mail.MailMessage

  $msg.From = new-object System.Net.Mail.MailAddress($FromAddress)

  foreach ($email_address in $email_addresses_array)

    {

      If ($email_address.Length -gt 0)

        {

          $msg.To.Add("$email_address")

        }

    }

  $msg.Subject = $msg_Subject

  $msg.Body = $Body

  $smtpClient = new-object System.Net.Mail.SmtpClient

  $smtpClient.Host = $SmtpServer

  $smtpClient.Send($msg)

}

Thoughts on the discontinuation of Microsoft's MCM/MCSM program

It's old news, I know, but Microsoft killed the MCM/MCSM program, announcing it just before the Labor Day holiday weekend. There isn't much I can say that hasn't already been said. Every SQL blogger out there seems to have weighed in already. Among them was a very popular Connect item calling for the program's reinstatement that contained a HUGE outpouring of support for the program and some thoughtful responses from Microsoft. Oddly, that Connect item seems to have disappeared.

Personally, I feel this was a mistake on Microsoft's part. Killing off the one certification program they have that truly means something for what appears to be financial reasons was a short-sighted move. If, on the other-hand, as Tim Sneath would like I to believe, the program was killed for lack of interest and participation, then I believe this was still a mistake. Doing so before having something to take its place, to solve the participation problem, still seems shortsighted. In either case, I hope they reconsider and implement a new program in its place. One that maintains the integrity of the program, and continues to really mean something.

If they had a plan by making the announcement just before a holiday weekend, the plan worked. I was out camping that weekend and backpacking the following week such that I didn't get the news until nearly two weeks after the fact. Initially, I was kinda crushed. I passed the SQL MCM knowledge exam last November at the PASS Summit and took the lab exam in late December. I narrowly failed, my report saying that I received 82% of the required score. I've been preparing most of the year for a retake and had penciled in a late October re-take. Then this news.

I wondered if I should even bother retaking the exam, now, with the program effectively dead on 10/1 or if I should go head and bump up my timetable for a retake and give it one more shot. In the end, I decided to retake the exam, which I did today, having just finished about an hour ago.

Why?

• In the hope that the program will reboot in some meaningful way and my hoped for 2008 MCM certification will grandfather me in, in some way.
• Because if bothered me that I didn't pass the first time
• Because I think the certification will still be meaningful, even if the program is dead.

One interesting tidbit. The exam proctor, Ralph something or other, wish I'd remembered to write down his last name, mentioned that they've extended the program through the end of the year and waived the 90 day waiting period for exam retakes. Which means if I fail again, I'll have one more shot if I can come up with the exam fee, It's odd, though, that I've seen no announcements of this extension, other than a blurb on the MCM Advanced Certification page that states: "If you have outstanding Masters or Architect exams to complete, please contact our Advanced Certification team at advcert@microsoft.com to arrange to take your exams before December 31, 2013." 

In any event, I'll see where my chips fell here in 4-6 weeks. I feel like I did better than I did last time, but don't feel like I aced it. It's going to be close. 

Speaking at SQLSaturday #206 in Madison, WI

I'll be presenting on SQL Server Data Tools (SSDT) for Visual Studio 2012 at SQLSaturday #206 in Madison, WI at Madison Area Technical College on April 6th. I'll be in the 4PM timeslot in Room 142B.

This will be a very similar talk to the one I gave at FoxPASS in February. The revised abstract:

In 2007, Microsoft introduced Data Dude for Visual Studio 2005 to help bring the processes, controls and tools for code management to Database Development that Application developers had been enjoying for ages. It had its problems, but it mostly worked and was a welcome step in the right direction. Many versions later we have SQL Server Data Tools for Visual Studio 2012 (and 2010). A more mature product that finally brings database development up to par with traditional application development and has many features data geeks, like ourselves, will truly love. This talk will provide an overview of SSDT’s features and show you how to get started using it to save time, implement better code controls and be a better DBA or database developer.