I have had to deploy database applications in a variety of scenarios and very early on in my career I reached the conclusion that any means of automating the deployment process was a "Good Thing".
Manual deployment introduces a point of failure for even the most diligent DBA, particularly when that deployment has to take place in hours when most sane people are soundly asleep. Where I work at present downtime costs big money (a years salary in lost revenue for every minute) hence the early start.
This means that you would be undertaking a manual process under the following conditions: -
- Under stress due to cost considerations
- On 3 or 4 hours disturbed sleep
- Severely decaffeinated
The SQLCMD and its predecessors OSQL and ISQL offer a means of automating such deployments. However SQL Management Studio offers the facility to use SQLCMD from within SQL scripts by using SQLCMD Mode.
SQLCMD Mode
SQLCMD Mode can be activated/deactivated from a menu option within SQL Management Studio as shown below.
When SQLCMD Mode is engaged then the specific SQLCMD commands are shown on a grey background as shown below
As you can see from the script above I have asked to connect to a database server called DBDevelopmentServer and run a query in the Adventureworks database.
Straight away we can see that it is possible to specify the server that a script is supposed to run on as part of the script.
This may sound like small beer but for me a typical deployment may involve tens of scripts all to be deployed to servers with remarkably similar names. Being able to state explicitly what server the scripts are to be run on gives a major advantage
- An incorrect server name will show up in any peer review
- The server name is "in-your-face"
User variables in SQLCMD Mode
The :setvar command is a useful addition in the SQLCMD arsenal.
We can refine our first sample script slightly to demonstrate its usage
By itself this does not look like a big deal but it demonstrates the following
- Connections can be made on user variables
- Databases can be specified in user variables
Running many files from a single INSTALL.SQL script
Ultimately we want to reach the point where we only have to run a single Install.SQL script to deploy an entire solution.
Fortunately SQLCMD mode has a suitable facility to allow this to happen. To demonstrate it I created a simple script called TestSQLCMD.SQL as follows
DECLARE
@UserName SYSNAME ,
@DeploymentTime CHAR(18),
@DeploymentDB sysname,
@CRLF CHAR(2)
SET@CRLF = CHAR(13)+CHAR(10)
SET @UserName = SUSER_SNAME()+@CRLF
SET @DeploymentTime = CONVERT(CHAR(16),CURRENT_TIMESTAMP),120)+@CRLF
SET @DeploymentDB = DB_NAME()+@CRLF
PRINT '***************************************'
RAISERROR('DEPLOYMENT SERVER: %s%sDEPLOYMENT DB: %sDEPLOYMENT TIME:%sDEPLOYER: %s',10,1,@@SERVERNAME,@CRLF,@DeploymentDB,@DeploymentTime,@UserName)
PRINT 'TestSQLCMD.SQL IN VSS UNDER Solutions\SQLServerCentral\SQLCMD'
PRINT '***************************************'
GO
SET NOCOUNT,XACT_ABORT ON
INSERT INTO Person.ContactType(
[Name],
ModifiedDate
) VALUES (
/* Name - Name */ N'Dave',
CURRENT_TIMESTAMP
)
RAISERROR('%i record(s) deployed to Person.ContactType',10,1,@@ROWCOUNT)
Ignore the code before the INSERT statement, it is purely a standard header I use in all my scripts to be able to verify that a script has been run on the correct server.
However, note that there is no database mentioned or connection specified. In fact I deliberately disconnected this query and saved it in my local "My Documents" folder as follows
C:\Documents and Settings\David\My Documents\SQLServerCentral\SQLCMD\TestSQLCMD.SQL
Our original SQLCMD mode script can then be modified as follows
This demonstrates something incredibly important
- We can control all our connections and the databases we use from a single file
- Even if a release engineer downloads our project scripts to a completely different directory they can specify that directory in a variable. Control is still from a single file.
- We can mix and match strings and variables with SQLCMD mode commands.
There is another consideration I should like to draw your attention to. In some cases I may want to run a single script on several servers or on several databases for a single deployment.
Two specific examples come to mind.
- Replication subscriptions where the @sync_type=N'none' where you have to create objects manually.
- Databases that are patterns where a fix applied to one should be applied to all.
This method of scripting allows me to run that same script many times automatically.
Output and errors
If you are going to implement a single INSTALL.SQL script then you have to make sure that the scripts that are called from INSTALL.SQL are pretty much bomb proof. They should not produce errors and should be safe even if they are rerun by mistake. In short we have to raise our game when it comes to our scripting skills.
We have to be much more rigorous with error trapping and pre-emptive checks when handing installation scripts over to a 3rd party. Of course our output and errors will still be shown on the screen but it would be safer to ensure that the output goes to specific text files so the installation can be reviewed.
With this in mind we alter our original script one more time.
Here you can see I have asked to put the errors and output into text files in the installation directory. The install.out file is shown below
Connecting to DBDevelopmentServer... *************************************** DEPLOYMENT SERVER: DBDevelopmentServer DEPLOYMENT DB: Adventureworks DEPLOYMENT TIME:2009-02-14 16:02 DEPLOYER: DBDevelopmentServer\David TestSQLCMD.SQL IN VSS UNDER Solutions\SQLServerCentral\SQLCMD *************************************** 1 record(s) deployed to Person.ContactType ContactTypeID Name ModifiedDate ------------- -------------------------------------------------- ----------------------- 1 Accounting Manager 1998-06-01 00:00:00.000 2 Assistant Sales Agent 1998-06-01 00:00:00.000 3 Assistant Sales Representative 1998-06-01 00:00:00.000 4 Coordinator Foreign Markets 1998-06-01 00:00:00.000 5 Export Administrator 1998-06-01 00:00:00.000 6 International Marketing Manager 1998-06-01 00:00:00.000 7 Marketing Assistant 1998-06-01 00:00:00.000 8 Marketing Manager 1998-06-01 00:00:00.000 9 Marketing Representative 1998-06-01 00:00:00.000 10 Order Administrator 1998-06-01 00:00:00.000 11 Owner 1998-06-01 00:00:00.000 12 Owner/Marketing Assistant 1998-06-01 00:00:00.000 13 Product Manager 1998-06-01 00:00:00.000 14 Purchasing Agent 1998-06-01 00:00:00.000 15 Purchasing Manager 1998-06-01 00:00:00.000 16 Regional Account Representative 1998-06-01 00:00:00.000 17 Sales Agent 1998-06-01 00:00:00.000 18 Sales Associate 1998-06-01 00:00:00.000 19 Sales Manager 1998-06-01 00:00:00.000 20 Sales Representative 1998-06-01 00:00:00.000 25 Sales Gurus 2007-12-14 11:56:08.950 36 Dave 2009-02-14 15:13:39.967 37 James 2009-02-14 15:14:40.780 Disconnecting connection from DBDevelopmentServer...
Conclusion
I first came across SQLCMD mode when working with Visual Studio Team Edition for database professionals. I was trawling through the various files that Visual Studio creates in order to form a deployment script and came across a file which simply listed every database object file prefixed by :r.
Digging around revealed that Visual Studio was just calling SQLCMD mode using an install.SQL file.
SQLCMD mode is useful for simple deployments where the release engineer will have access to SQL Management Studio. If Microsoft ever consider extending SQLCMD mode I would suggest that they look at error handling and some if…then…else constructs to aid automated deployments.
For this reason and given the time I prefer to use SQLCMD.EXE from a windows command file as this already has the ability to do such things.
