Custom Database Scripts / Load Balanced Web Servers

FELKERD

A couple of more questions:

1) So I am trying to get a better understanding of how to deploy our database changes. I found a "publish for deployment" option in SQL Management Studio but I am still not sure what it creates in terms of a script. A lot of times we may have tables in development that may not be ready for inclusion in the test/production release. We currently have SQL Compare and can use it to generate a change script of only the applicable objects, but I am not sure how to incorporate that into the deployment package. Any help you could give here would be appreciated.

2) I understand that DM will create a new directory and simply update IIS, but how will that work in a case where you have load balanced web servers? Does it update them all at once?

chirayu

1) So I am trying to get a better understanding of how to deploy our database changes. I found a "publish for deployment" option in SQL Management Studio but I am still not sure what it creates in terms of a script.

When Deployment Manager deploys database changes, there are two ways it can do this:
- Dynamic database deployments- When you use the SSMS add-in to package the database, the upgrade script to be run on the target database is generated at deploy time, by comparing the version on the target machine to the packaged version. You don't get to see the script before it is run. However, if there is any data loss then the deployment will by default be aborted, but this is configurable.
- Static database deployments- When you use sqlCI to package the database it will generate the upgrade script at package time for any versions currently deployed. If there is any data loss then the package will not be created, but this is configurable. You can open up the package and see what the upgrade scripts are, etc. Editting the scripts requires you to edit the package, which is slightly tricky but possible. At deployment time, Deployment Manager will see if there is an applicable upgrade script in the package, if there is, it'll run it, if there isn't, then it'll fallback to dynamic database deployment mode.

A lot of times we may have tables in development that may not be ready for inclusion in the test/production release. We currently have SQL Compare and can use it to generate a change script of only the applicable objects, but I am not sure how to incorporate that into the deployment package.

It would work best if you package up a database that you definitely want to deploy in its entirety. This may mean that you want to use SQL Compare to create that database in the first place, i.e. you'd use SQL Compare to sync from one dev database to another database in dev to get all the changes you want to deploy in just one database; then you package that database with the SSMS add-in, and then you can deploy it with Deployment Manager. This means you get all the other benefits of Deployment Manager, such as deploying with app code, the dashboard, etc...

You can also theoretically use filter rules to achieve the same aim. Filter rules allow you to identify the objects that will be compared at deployment time. So, if you exclude a database object by using filter rules, it won't be compared at deploy time. A filter file can be applied during deployment:
- As you may know, these filter files can be created in SQL Compare, if you select the Filter Setup button the filter options will be displayed. This gives you the ability to set up more complicated rules. If you save the filter file by the name 'Filter.spcf' and drop it in the top level and create a new version of the package, this filter will be read at deployment time.
- If using SQL Source Control you can commit a filter rules file to your database, these can be changed using Other SQL source control tasks – Edit filter rules, then committed to the repository, however they would be applied for your commit list, so you might want to create a replicated/copy of your repository using SQL Compare.

2) I understand that DM will create a new directory and simply update IIS, but how will that work in a case where you have load balanced web servers? Does it update them all at once?

When deploying a package to a whole bunch of machines, Deployment Manager will update them all simultaneously. There may be slight differences in when it is deployed if one machine is significantly faster than another, but we try to minimize the chance of this occurring.

That should be hopefully sufficient for you, but if not then get in touch. You could always use something like http://blogs.msdn.com/b/benjaminperkins ... cture.aspx and only use Deployment Manager to deploy to the primary, but there shouldn't be a need to, since Deployment Manager is designed to handle this use case.

chirayu

If you save the filter file by the name 'Filter.spcf'

There is a typo, the filter file should actually be called 'Filter.scpf' and should be present in Scripts/ folder which is at the top level.

FELKERD

Thank you for the guidance and thorough response to my questions! We will try these out and report back to the thread.