Archive for the 'SQL Server' Category

One of the responsibilities of my current position as a SQL Developer is to prepare the scripts for deployment. For quality assurance reasons we store all of the database objects in a source control system, so each object is stored in a separate file. This can make deployments very tedious, depending on the volume of files.

Disclaimer

These processes are my processes for an internally managed application. These scripts and processes are not distributed in any way, and quite frankly are not suited for that purpose.

This does not cover any T-SQL best practices. There are far better articles written by far more knowledgeable people covering these topics. Click here for a list of best practice articles.

The Development

Here are my general guidelines for deployment, in no particular order:

1. Each script must be able to be executed multiple times without error.

There is nothing worse that running a script to test a deployment and then getting an error because an object already exists or a key violation because the data already exists. All scripts must check for the existence of the finals state before doing anything else. For stored procedures, this may me a conditional drop and then create and for tables this may mean checking for column existence before running an alter statement.

2. Object permission must be part of the object script.

Your mileage may vary with this one, but I am a fan of explicit permission granted to a database role for each database object that is being deployed. For newer architectures your roles may have blanket permissions to a schema, but I have not seen many instances of security set up this way.

Having the permission set at the end of the script (just in case you drop an re-create it :-)) will save a lot of headache if you ever need to setup the application in a new environment. NOTE: Please do not forget the batch separator (i.e. “GO”) in the script. I hate seeing permission errors where a stored procedure is trying execute permissions to itself.

3. Group the data scripts by table.

If you follow guideline #1 this shouldn’t be much of an issue, but if you have the same or worse, similar, updates in multiple places you could in a world of hurt.

4. Try to group like objects.

I try to keep like objects together. With some exceptions I will deploy objects in the following order:

Tables – With the exception of using a function in a default value or check constraint, it is a pretty safe bet that you can deploy all table changes first without error. The most tedious part of deploying tables is their relationships. Since they are dependant on one another, order is important. I have not found a perfect way to deal with this dependency.

Views – Again, it is possible to use functions as part of a view, but I wouldn’t recommend it. It is also possible to reference another view as part of a view definition, but I would not recommend that either. Pay attention to the order, just in case.

Functions – In SQL Sever you cannot call a stored procedure as part of a scalar or table valued function, so deploying these before the stored procedures is advisable.

Stored Procedures – These are next to last because they can use any of the previous three objects. They may, however, be dependant on one another, so watch the order here as well.

Triggers – Triggers can be added to tables or view and they can use stored procedure or functions, so I save these for last.

5. Take as much care with the rollback scripts as you do with the future scripts.

What can be worse than beginning a deployment and then realizing that you have to rollback. After the rollback completes, you realize that you just dropped a primary table in your database that you just meant to alter. Oops.

For this reason I will hand write table rollback scripts. I use a script generator like ScriptDB to generate all of the other rollback scripts a week or so prior to deployment to capture any run fixes that were deployed during the development process.

I maintain a 1:1 relationship between deployment and rollback scripts. I execute the rollback scripts in reverse order to the deployments scripts. No muss, no fuss and no additional lists of scripts to maintain.

6. Don’t forget the jobs!

If you update requires job changes, add those to source control for deployment as well. All of the objects that are part of the deployment should be scripted.

7. Store the deployment order some place safe.

The largest deployment that I have worked on to date was over 600 individual scripts, with many exceptions to the steps listed above. Rebuilding that list would have taken a large amount of effort and testing, which I really did not have the time to do.

I prefer to store them in a database table table so that the list is available to other developers to make the necessary changes. I never want to be the one left holding the ball, let alone the one to drop, so let’s keep this in a safe place.

The Deployment

Take the list that you have so tediously maintained throughout the development process and script it into a file. I use sqlcmd to deploy all of the database objects, so this is a relatively simple process. Be sure that error handling is written into the script. I prefer that the batch ends when an error is encountered. If you followed #1 above, you can fix any errors and start the batch from the beginning.

The table that I use to store the scripts has the definition below. I have a template parameter for the database in the script. Use Ctrl+Shift+M or click the e “Specify Values for Template Parameters” button to populate this field.

To create the batch file used for deployment I use to SQL outlined below. I find this option to be very flexible. Run the result to text. I hate to use cursors like everyone else, but this ensures that I get the proper format out.

Running on a recently updated to SQL Server 2005 cluster, we were noticing some performance issues with one stored procedure in particular. We initially blamed the issue on optimizer changes, but were looking a fix that did not involve and object change.

Someone came up with the idea (not me) to refresh the view. I am aware that a view that contains the dreaded “SELECT *” may not contain new table columns until the view is refreshed, but I have never actually run into this issue in real life. In our situation the view definition had not changed, nor had the underlying objects, but refreshing the view resolved the performance issues.

So on that note i wrote a quick little cursor up refresh all views in a database. I hope someone finds this useful.

We have a service that polls a myriad of things several times an hour to send pages and alerts to the appropriate people based on a specific set of criteria. One of the things that it polls is the SQL job history, which can be helpful to be proactive in catching long running jobs or even if a job gets disabled.

The time that a job is scheduled to run and it’s run duration is saved as an integer in hhmmss format, so converting this to the datetime datatype requires some manipulation. You can find a lot of ways to do this using string functions, which is the way we had completed the conversion … until is started erroring.

The statement below will convert the Integer value to the datetime datatype using mathmatical functions instead of string functions, which is less prone to error.

The first thing that we will do is extract the hours, minutes and seconds into their separate parts using the modulo operator. Once we have that we will convert these values into seconds (the smallest interval we are working with) and add them together. Once we have the offset value in seconds from midnight we use the dateadd function and add the seconds to the run date. Viola! And it’s even a little faster.

We are working on code generation and thought it would be neat to pull information from description (where we intend to put validations for ex. DOB – No future date). The following query did that trick.

How many times have you had to open an existing SQL object and copy the header information to add to a new procedure, view or function? How many times have you done this and forgot to change the information in the header? Do you like standards? If you answered “Yes!” to any of these questions then templates are for you! Microsoft SQL Server Management Studio comes with a plethora of built-in templates, but this little gem of a feature also allows you to create your own!

To open template explorer, select View -> Template Explore or press Ctrl+Alt+T. This will open a side bar (by default, but you can change it) with the templates installed by default. To add your own templates, right-click on “SQL Server Templates” and then “New” from the context menu. This will give you the option to create a new folder or template. I prefer to keep my user-created templates in their own folder, since that makes it easier to transfer templates between machines.

Let’s create a folder called “CodeSnippets” as shown in the screen shot below.

Template Context Menu (Folder)

Now that we have our folder created, let’s create some templates. We will create a basic header template and a stored procedure template. To create our templates, Right click on the newly create folder and select New -> Template from the context menu.

Template Context Menu (Template)

Now that we have the blank templates created, let’s add some content. Right click on the template and select “Edit” from the context menu.

Template Context Meny (Edit)

From here you can write or paste your content into the template.After creating your templates, usage is as simple as dragging and dropping your template into the proper location in the query window.

I mentioned earlier about transferring templates between machines. To move your templates between machines browse to