How to Automate SQL Mirroring

Starting with SQL 2005 SP1 Standard Edition, you had the ability to setup and configure Database Mirroring.  This was a step up from the previous Data Replication options of Log Shipping (Only available in SQL 2000 Enterprise), and Database Publishing/Subscription and more cost effective than SQL Clustering as a means to provide local redundancy for your databases.  The process of setting up DB Mirroring is a manual one.  You must either use the management Console UI Wizard, or perform the like steps through individual T-SQL Scripts.  There is currently no way to configure SQL initially to create Mirrors of your databases that you create by inputting the name of another SQL Server somewhere.

At one of my previous jobs, I was tasked with automating this for a Hosting Environment where the creation of Databases was dynamic and part of a sign-up process for a SaaS web application. Since many databases could be created throughout a given day, it was desired to have a means to automatically create redundancy for those databases via SQL Mirroring.  So I starting investigating how this could be done.  The individual steps were easily scriptable to setup mirring.  It was the creation of the Database event that I needed to have visibility on since databases could be created from the SQL Server Management Console, the Web Application Managed Code, or the Administration Management Console for the web application.  The logical place for this was on the SQL Server.  I had worked with Triggers before so I decided to start looking there.

Regular DML triggers would not work since those fire in response to UPDATE, INSERT, or DELETE statements on a table or view. DDL Triggers however, fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP.   This was obviously a perfect fit for the solution as I needed to have visibility over CREATE DATABASE events.  Now I was ready.

Prerequisites and Preliminary Steps

• Setup and Configure a Secondary SQL Server (Should be identical to source server – version, service pack, and license type)
• Enable TCP/IP protocol for SQL Server in Connections
• Setup a Network Share for Backup ad Restore operations – something like \\dfsroot\sqlbackup
• Enable use of xp_cmdshell stored procedure (Surface Area Configuration Tool or Facet if using SQL 2008)

It is unfortunately not possible to just create a trigger to perform all the actions you desire – I tried that first :-).  So this method involves the creation of 4 components – an event sink table, a workload stored procedure, a Data Definition Language (DDL) Trigger, and a SQL Server Agent Job which executes the aforementioned stored procedure on a desired interval.

Script 1 – Create an Event Sink Table
This table will hold the names and events of the SQL events where a CREATE database action occurred. The event type is really no necessary since we are ony interested in the Database Name

USE [master]
GO
/****** Object:  Table [dbo].[new_db_table]    Script Date: 05/19/2008 15:26:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[new_db_table](
[name] [nvarchar](100) NULL,
[event] [nvarchar](100) NULL
) ON [PRIMARY]

Script 2 – Create the Automation Stored Procedure
This is the main script that does the job of checking the actions table, performing the initial backupsIn SQL 2005, there is no database event for an attach database action even though in reality this is a CREATE_DATABASE event.  This is resolved in SQL 2008 which will cause the trigger to add an entry in the actions table for both ATTACH and CREATE actions.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_auto_mirror_config]    Script Date: 07/10/2008 10:56:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

— ============================================================================
— Author:        <Jeff Sani,jeffs@sitemasher.com>
— Create date: <4/9/2008>
— Description:    <This stored proc automates the configuration of db mirroring>
— Syntax:      <exec sp_auto_mirror_config>
— =============================================================================

CREATE proc [dbo].[sp_auto_mirror_config]
as
declare @dbname sysname, @bckstmt nvarchar(500), @cmd varchar(250), @bupath varchar(100)
declare @mirrorsp nvarchar(100), @mirrorsql nvarchar(500), @altersql nvarchar(250)
declare @primarysrvr nvarchar(50), @mirrorsrvr nvarchar(50), @witnesssrvr nvarchar(50), @domain nvarchar(50)

–set your sql server and backup paths here
set @bupath = ‘\\sql1\sqlbackup’
set @primarysrvr = ‘sql1’
set @mirrorsrvr = ‘sql2’
set @domain = ‘.staging.local’
set @witnesssrvr = ‘smres2’
set @mirrorsp = @mirrorsrvr + ‘.master.dbo.sp_executesql ‘

begin
if (select count(*) from new_db_table where event = ‘CREATE_DATABASE’) > 0
begin
create table #userdbs (name sysname)
insert into #userdbs select name from new_db_table
declare cdb cursor for select name from #userdbs
open cdb
fetch cdb into @dbname
while @@fetch_status = 0
begin

–Check to make sure that Auto_Close and Auto_Shrink DB Properties are correct and that Recovery is Full
set     @altersql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set AUTO_CLOSE off’
exec (@altersql)

set     @altersql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set AUTO_SHRINK on’
exec (@altersql)

set     @altersql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set RECOVERY full’
exec (@altersql)

–perform initial database backup
set @bckstmt = ‘backup database ‘ + char(91) + @dbname + char(93)+ ‘ to ‘ +
‘disk = N’ + char(39) + @bupath  + ‘\’ + @dbname + ‘.bak’ + char(39)
exec (@bckstmt)

–perform initial database log backup
set @bckstmt = ‘backUp log ‘ + char(91) + @dbname + char(93)+ ‘ to ‘ +
‘disk = N’ + char(39)  + @bupath  + ‘\’ + @dbname + ‘_log.bak’ + char(39)
exec (@bckstmt)

–perform database restore on linked remote mirror sql server
set @bckstmt = ‘restore database ‘ + char(91) + @dbname + char(93) + ‘ from ‘ +
‘Disk = N’ + char(39)  + @bupath  + ‘\’ + @dbname + ‘.bak’ + char(39) + ‘ with norecovery, replace’
exec @mirrorsp @bckstmt

–perform database log restore on linked remote mirror sql server
set @bckstmt = ‘restore log ‘ + char(91) + @dbname + char(93) + ‘ from ‘ +
‘Disk = N’ + char(39)  + @bupath  + ‘\’ + @dbname + ‘_log.bak’ + char(39) + ‘ with norecovery, replace’
exec @mirrorsp @bckstmt

–Initiate the mirroring on The Mirror server:
set     @mirrorsql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set partner= N’+ char(39) + ‘TCP://’ + @primarysrvr + @domain + ‘:5022’ + char(39)
exec @mirrorsp @mirrorsql

–Initiate the mirroring on The Primary server:
set @mirrorsql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set partner= N’+ char(39) + ‘TCP://’ + @mirrorsrvr + @domain + ‘:5022’ + char(39)
exec (@mirrorsql)

–Enable the mirroring session on the Witness server:
set     @mirrorsql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set witness= N’+ char(39) + ‘TCP://’ + @witnesssrvr + @domain + ‘:5022’ + char(39)
exec (@mirrorsql)

–cleanup
delete from new_db_table where name = @dbname
set @cmd = ‘del ‘ + @bupath  + ‘\’ + @dbname + ‘.bak’
exec xp_cmdshell @cmd
set @cmd = ‘del ‘ + @bupath  + ‘\’ + @dbname + ‘_log.bak’
exec xp_cmdshell @cmd
fetch cdb into @dbname
end
close cdb
deallocate cdb
drop table #userdbs
end
end

Script 3 – Create the Trigger
This will fire when an event occurs that matches CREATE_DATABASE and will populate the event sink table with the DB name.

/****** Object:  DdlTrigger [trg_MirrorDDL]    Script Date: 05/19/2008 15:31:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [trg_MirrorDDL]
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @data XML;
DECLARE @eventType sysname;
DECLARE @dbname varchar(100);
DECLARE @mirrorsql varchar(500);

SET @data = EVENTDATA();
SET @eventType = @data.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘sysname’);
SET @dbname = @data.value(‘(/EVENT_INSTANCE/ DatabaseName)[1]’, ‘sysname’);

–Add to new_db_table
Insert new_db_table(name,event) select @dbname,@eventType where not exists (select * from new_db_table where name = @dbname);
END

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trg_MirrorDDL] ON ALL SERVER

Script 4 – Create the SQL Agent SVC Job
Main purpose of this job is to monitor the even sink for new entries.  I had thought about having a tigger on the vent sink table, but you might not want to have the automation be on-demand so I thought a job which governed the execution of the workload stored procedure, would be better.

USE [msdb]
GO
/****** Object:  Job [Mirroring Agent]    Script Date: 05/19/2008 15:32:54 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/19/2008 15:32:54 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Mirroring Agent’,
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’SMNET\administrator’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Run Mirroring Stored Procedure]    Script Date: 05/19/2008 15:32:55 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Run Mirroring Stored Procedure’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’USE [master]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[sp_auto_mirror_config]

SELECT    ”Return Value” = @return_value

GO’,
@database_name=N’master’,
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Mirror Agent Schedule’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=5,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080410,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

There may very well be a more eloquent way of doing this, but this method does work. Some other relevant info – While there is a really high limit of 32K databases that you can create on one SQL server, you would never want to do this as it would become a management nightmare.  Further, you will reach a limit on the host at which mirroring (and really any feature such as replicaiton that uses TCP socket connections) will cease to function.  I found this number to be 100.  Having multiple instances on the same box does not help as this does not decrease the number of sockets consumed per database for mirroring or replication.  Microsoft support recommends no more than 50 databases per server in a mirrored configuration.  So if you are provisioning for the masses, make sure you have enought physical or virtual SQL server instances to accommdate the number of customers you plan on supporting.  Hope this helps!

Server Name Indication and SSL VPN

The Problem

Many People are unfamiliar with Server Name Indication (SNI) despite having been introduced as an extension to the TLS protocol back in 2005. In a nutshell, when client computer browsers or SSL based VPNs are negotiating encryption with a server, there is no information which can be gleaned by the server in order to determine which virtual host the client is actually requesting.  This is due to the fact that the hostname of the subsequent request is contained in the encrypted header which would not be visible until after the received data was decrypted as it made its way down the stack.  This is problematic with respect to virtual hosting since each server or appliance can serve many hosts through the same address.  If it is desired to secure the data of that host through SSL, then a 1:1 mapping of hostname to IP address is currently required.

Client: (TLS Handshake) Hello, I support XYZ Encryption.
Server: (TLS Handshake) Hi There, Here is my Public Certificate, and lets use this encryption algorithm.
Client: (TLS Handshake) Sounds good to me.
Client: (Encrypted) HTTP Request
Server: (Encrypted) HTTP Reply

What about ‘STARTTLS’ or TLS ‘Upgrade’ in HTTP/1.1?

STARTTLS is another standard which is commonly used by protocols such as SMTP, POP, IMAP, and LDAP.  Back in the day, it was common practice to have parallel secure ports for most protocols.  For example,  with SMTP, POP, IMAP, and LDAP, and HTTP you have 25/465 110/995 145/993 389/636, and 80/443 respectively. The idea of  STARTTLS was born when the IETF which governs internet assigned numbers and ports decided back in 1997 at some meeting that the issuing of paralell “secure” ports for all protcols should be depricated.   With STARTTLS, when the connection to the server host is established, the client sends a plantext command with the virtual host name.  This has enough information for the server to decide which certificate to offer for the SSL/TLS handshake.

Client: (TLS Handshake) Hello, I support XYZ Encryption.

Client: (Cleartext) I am using server ‘access.mycompany.com‘

Server: (Cleartext) By The Way, I also support TLS Encryptionn.

Client: (Cleartext) Lets use Encryption, aka ‘STARTTLS’.

Client: (TLS Handshake) Hello, I support XYZ Encryption.

Server: (TLS Handshake) Hi There, Here is my Public Certificate, and lets use this encryption algorithm.

Client: (TLS Handshake) Sounds good to me.

Client & Server: (Encrypted) Exchange Data

A similar method for web browsers, and SSL VPN clients was derived in the HTTP/1.1 specification and is called TLS Upgrade. HTTP/1.1 TLS Upgrade method can be applied to upgrade an open HTTP connection. In a nutshell, the client would include this in a request:

GET http://access.mycompany.com/securestuff HTTP/1.1
Host: access.mycompany.com
Upgrade: TLS/1.0
Connection: Upgrade

The server in turn might respond with:

HTTP/1.1 101 Switching Protocols
Upgrade: TLS/1.0, HTTP/1.1
Connection: Upgrade

The main benefit with these methods are that you can have both naked and secure traffic traversing through the same  port.  Main problems to this and likely why these methods have not been adopted are that all methods would require that any proxy servers in between the client and server also support this method.  A proxy server that did not acknowledge it or perhaps strips the command (could also happen on a legacy firewall), would potentially present a man-in-the-middle attack.  A lesser issue might be that you have a user perception issue as there is a certain familiarity with port 443 being the “secure” port.  On the server end of things, you would also need to have the unsecure port open for the application in question which may not be the case.

The Solution

An extension to SSL/TLS called Server Name Indication (SNI) addresses this issue by sending the name of the virtual host as part of the SSL/TLS negotiation. This enables the server to bind the correct virtual host early and present the browser with the certificate containing a CN matching that in the SNI header.  This method also has far fewer complications associated with it as compared to TLS Upgrade or STARTTLS.  The SNI extension is described in gross detail here. With SNI, you would have a sequence like:

Client: (TLS Handshake) Hello, I support XYZ Encryption, and I am trying to connect to
‘access.mycompany.com‘.
Server: (TLS Handshake) Hi There, Here is my Public Certificate, and lets use this encryption algorithm.
Client: (TLS Handshake) Sounds good to me.
Client: (Encrypted) HTTP Request
Server: (Encrypted) HTTP Reply

But Don’t Browser’s and Servers need to support this extension in order for this to work?

Yup, that is the idea.  As with any RFC, extension, or modification you have to have adoption by the software developers, and hardware vendors which in turn are driven by the adoption or request of the technology by the IT community.  The latter is only done through education and practical application examples which is one of my main drivers for writing this blog post.  At the time of this writing, there are no known Remote Access Appliances which support this RFC extension.  Below is the list of known browsers, servers, and SSL application libraries which do support the SNI extension:

Browsers

• Mozilla Firefox 2.0 or later
• Opera 8.0 or later (the TLS 1.1 protocol must be enabled)
• Internet Explorer 7 (Vista, not XP) or later
• Google Chrome
• Safari 3.2.1 Mac OS X 10.5.6

Servers

• Apache with mod_gnutls or mod_ssl
• Cherokee if compiled with TLS support
• New versions of lighttpd 1.4.x and 1.5.x
• Nginx with an accompanying OpenSSL built with SNI support
• OS X 10.5.6

Libraries

• Mozilla NSS
• OpenSSL
  • 0.9.8f – not compiled in by default, can be compiled in with config option ‘–enable-tlsext’.
  • Unreleased 0.9.9 is likely to include SNI compiled in by default.
• GNU TLS

Unsupported Operating Systems Browsers, and Libraries

The following combinations do not support SNI.

• Windows XP and Internet Explorer 6 or 7
• Konqueror/KDE in any version
• Microsoft Internet Information Server IIS
• Sun Java System Web Server
• Microsoft.Net
• Sun Java JSEE

What SNI could add to SSL-based VPN Solutions?

So what does this mean with respect to Remote Access Solutions such as Citrix Access Gateway, F5 Firepass, or Juniper Secure Access remote access solutions?  The benefits of adopting support for SNI are wide an varying, but here is my first pass at a few:

• Since the SNI would be presented to the access appliance before the SSL negotiation finalized, specific SSL policies such as supported cipher suites, could be bound to the session.   This would be useful where you needed to meet more stringent security requirements such as FIPS level 1/2 for specific hosts, or where you had a client application which used a specific type of encryption that you needed to utilize.
• As cloud computing is becoming more prevalent, service providers are going to need a means to offer customers secure access to their applications and content.  Since many cloud services are based on anycast addresses (floating IP), CNAME records, and also servicing 1000’s of users, a 1:1 option for customer:IP is not practical or possible. SNI presents a cheap, workable alternative to having no secure offering.
• Enterprises who wish to publicly expose their intranet or line of business applications securely may want to do so through a remote access appliance, but not want to allocate multiple public IP addresses.
• Businesses who have only been allocated a single IP address and are using Port Address Translation (PAT) to serve up multiple applications.  This is actually pretty common since many businesses are provisioned with ADSL which uses DHCP assign IP addresses. Most companies use a remote access device as an all-in-one solution for outbound RNAT, inbound VPN, and line of business applications, and firewall.

I hope to see one of these vendors include support for this little-known, but extremely-practical extension to the TLS protocol in the near future.  If you manage to find your way to this post, and you are an IT professional, I would love to hear your comments.

Saving Your SQL Server Connections in MSSMS

So one of the most frustrating things I noticed when I started using SQL back in the day was that you had to connect to each SQL server individually.  This can be rather annoying when you are managing multiple SQL server farms.  So a new feature of the Microsoft SQL Server Management Studio (MSSMS) that I discovered through curiosity was the concept of “Registered Servers”.  Basically, as the name infers, these are SQL servers that you have setup connections to with the appropriate authentication type which you register with the MSSMS.   You can even create groups of these servers so that you can logically separate them by function or location.

Configuring Registered Servers and Groups

To access Registered Servers, you simply click on the View Menu and select Registered Servers. This will open another tab view next to Object Explorer which opens by default when you launch the MSSMS.

From here you are presented with the option to connect to Database Engines, Reporting Services, Analysis Services, etc.  Obviously we are talking about SQL Servers here so you want to look at Database Engines.  In the tree, you will see the Default Local Server Group.  You are not restricted to use this group and can make your own logical groups as you see fit.  The one recommendation I can make is to create groups of SQL Servers that you desire to open simultaneously. 

Once you create the groups, you will add or “Register” SQL database engines into each group.  Simply clicking on “New Server Registration” will take you to the dialog where you specify the SQL Server Name, IP address, or Server Name\Instance Name,  and the authentication required to connect to that server.

Since with Windows Authentication, the authentication token is inherited from the Logged On user, there is no need to save the credentials and no option to.  However if you choose SQL Authentication, and also desire to streamline opening all the connections simultaneously, you will need to select the option to “Remember password” which will be presented to you if you do choose the SQL Authentication type.

Opening Connections Simultaneously

Ok, So you have added all the logical groups and servers that you desire.  So what is the trick to opening these in Object Explorer?  This is actually quite simple.  All you need to do is Switch to the Registered Servers view once you open the MSSMS.  Close the initial dialog which will prompt you for connection information once the studio is loaded (This occurs due to the fact the default view is Object Explorer and there are no connections open  – at the time of this writing, there is unfortunately no way to change this or configure as an option).

Once you are at the Registred Server View, you can right-click the group of servers you desire to open connections to and select “Object Explorer” from the context menu.  This will switch you back to Object Explorer view in the MSSMS, automatically open connections to all the servers in that particular group, and expand the Object tree.

Conclusion

So while this is not totally streamlined, the method presents a better alternative to having to open each connection individually.  I am hoping that in the future the MSSMS development team will at least provide a means to open Registered Server Groups from the initial start-up dialog so that you have one less step in the process. It would also be nice to be able to highlght multiple Server Objects in the Object Explorer and be able to dynamically create a Registered Server Group from those selections.  Enjoy!