Allen Kinsel - SQL DBA

SQL Server, SQLPASS, and other random things

Using Aliases with SQL Server

By Allen Kinsel, 1 month and 17 days ago

Even an old dog can learn new tricks

Where Can I get a dog like this?
I had an Aha! moment recently.  For my entire career as a DBA I have generally considered aliases for connections a workaround for bad behaving applications.  Whenever someone said «alias» my mind immediately heads to SQL Server client configuration aliases which I try to avoid if at all possible (since they are configured on each client)  It never snapped to me until recently that DNS aliases may be a good solution to a few problems  we're currently experiencing.

For disaster recovery reasons, as well as for manageability reasons we have decided to start using DNS aliases for every application connection to database servers.  This should allow us to have the luxury of moving databases from server to server without having to reconfigure multiple applications which would normally be a whole process in itself since the code was already migrated to production which is locked.

Using DNS aliases should also allow us to swap highly important applications over individually to a remote datacenter, which could have less computing power, without having to switch every application and thus kill the performance of that standby server.


Keep reading →

Related posts

Share this post

Tags

Allen Kinsel on Twitter

Categories

Most Recent Posts

Subscribe

Archives

Next Posts

Latest comments

  • Whose got my DAC?:
    • Allen Kinsel: They might not be too happy about that but, hopefully you know who they are and can contact them before killing their session, if you cant get a response and its an sleeping connection, then out comes the KILL command!
    • Jack Corbett: Wonder what the person on the other end would think if you killed their connection.
  • PASS TPS #1:
    • AjarnMark: Allen, keep up the writing! This type of transparency into the process and efforts that it takes to put together such a phenomenal event as the Summit is great!
    • Andy Warren: Allen, I hope you'll write a LOT about the efforts of the program committee. There's a lot of good/hard work that gets done there and I think we need to share that. And when things go wrong - like the sessions counts - that's ok too, good to let everyone know that we know something...
  • SQL Active Directory User ID SID Mismapings:
    • Allen Kinsel: Awesome, one of these days Im going to get a lot better at Powershell, thanks for sharing!
    • Chad Miller: Powershell and .NET will also work. From sqlps: PS SQLSERVER:\SQL\Z002\SQL2K8> invoke-sqlcmd -Query "select name, sid from sys.server_principals where type IN ('U','G')" | foreach {add-member -in $_ -membertype noteproperty SSDL (new-object security.principal.securityidentifier($_.SID,0)).ToString() -passthru} | where {$_.SSDL -eq 'S-X-X-X-X'}
  • Using Aliases with SQL Server:
    • Ken: I'm currently in a migration that will be making use of dns aliases, and have done some testing of moving from a default instance to named instances. You actually can use this still for moving applications from one named instance to another, you have to configure your named instances to listen on the default...

Most commented posts