One of the most time consuming things when I am doing a build is to figure out which stored procedures have changed since the last build and put then into a script to apply when upgrading to the current build.
Does anyone have any suggestions on how this might be automated to make it easier?
One thought I had was to use labels and the API to get the objects that are different between the labels, but I haven't been able to figure out how to get this accomplished.
Anyone have any thoughts?
Thanks
Automating Database Build Scripts
Moderator: SourceGear
This might help...
Are you using SQL Server 2000 and VS.NET?
We have to make Database schema updates all of the time. In VS.NET, you can create a Database project. The purpose of this project is to house scripts that can modify a SQL2k database. Be sure to add the project to source control.
The first time you make a schema change, on the top left hand corner of the DB tool bar, you'll see an icon that says Generate Change Script when you hover over it. Click it, and it will show you the SQL script that reflects the change you're making. It will also give the option to automatically save change scripts as you make changes - CHECK THIS OPTION.
Naming these scripts is very important, because SQL script HAVE to be run in order. Remember that when you change schema, SQL usually drops a table and then rebuilds it. So - if you have a table with an ID and NAME column, and then you change NAME to FIRST_NAME, and then you change FIRST_NAME from 30 to 50 in size - the two scripts that do this better run in order - otherwise, bad, BAD, BAD things will happen.
In order to ensure scripts run in order. use a naming scheme. For instance, I name them by version, and then assign a script number. For instance - for version 7.2.5 of my software, I have scripts:
7.2.5.100 - AR - Change name
7.2.5.105 - AP - Update size
7.2.5.110 - DB - Make schema change
Notice I start at a 100 and use three digits - the sorting is alphanumeric, and as such, 7.2.5.90 would come before 7.2.5.100 - so using three digits is important. I also use the initials of the developer who made the schema change.
Separating the files by increments of 5 allows for the flexibility to put queries in between two scripts that need to be inserted - for instance, let's say script #110 is a schema change that changes the ALLOWNULLS property of ID column to false. But you forgot - on your live site, that column has nulls! So maybe you need to insert the following script #107:
update table set id=0 where id=null
So that the script doesn't break when it goes live.
What about stored procedures?
Those are even easier. Just click on the database project, select Add SQL Query, and enter (for instance) 7.2.5.115 - AR - Add new SP for saving. A new window will popup, and you can stick your SP CREATE script in there - or any other script or query for that matter.
If each developer does this, you'll have a whole list of change scripts, IN ORDER, to run for deployment. If you right click on one, and select Create Command File, VS.NET will create a .cmd file that will run all of these scripts for you. It's a bit tricky, just make sure you tell SQL when you create the script whether you're using Win integration or not - it's kinda funny about not letting you change this after you build the script.
It's IMPORTANT that this is done consistently. These scripts AREN'T transactional, so if one fails, your database could be left in an inconsistent state. Worse - if there's a schema difference between a table you're trying to update on your production server and dev machine, the table could be emptied out for good. I'm speaking from experience here. So when you do this, make SURE you have a staged COPY of the database, exact copy, that you run the script on first to make sure it works - and make sure you backup the database if you want the belt and suspenders approach.
Does this help?
Andrew
We have to make Database schema updates all of the time. In VS.NET, you can create a Database project. The purpose of this project is to house scripts that can modify a SQL2k database. Be sure to add the project to source control.
The first time you make a schema change, on the top left hand corner of the DB tool bar, you'll see an icon that says Generate Change Script when you hover over it. Click it, and it will show you the SQL script that reflects the change you're making. It will also give the option to automatically save change scripts as you make changes - CHECK THIS OPTION.
Naming these scripts is very important, because SQL script HAVE to be run in order. Remember that when you change schema, SQL usually drops a table and then rebuilds it. So - if you have a table with an ID and NAME column, and then you change NAME to FIRST_NAME, and then you change FIRST_NAME from 30 to 50 in size - the two scripts that do this better run in order - otherwise, bad, BAD, BAD things will happen.
In order to ensure scripts run in order. use a naming scheme. For instance, I name them by version, and then assign a script number. For instance - for version 7.2.5 of my software, I have scripts:
7.2.5.100 - AR - Change name
7.2.5.105 - AP - Update size
7.2.5.110 - DB - Make schema change
Notice I start at a 100 and use three digits - the sorting is alphanumeric, and as such, 7.2.5.90 would come before 7.2.5.100 - so using three digits is important. I also use the initials of the developer who made the schema change.
Separating the files by increments of 5 allows for the flexibility to put queries in between two scripts that need to be inserted - for instance, let's say script #110 is a schema change that changes the ALLOWNULLS property of ID column to false. But you forgot - on your live site, that column has nulls! So maybe you need to insert the following script #107:
update table set id=0 where id=null
So that the script doesn't break when it goes live.
What about stored procedures?
Those are even easier. Just click on the database project, select Add SQL Query, and enter (for instance) 7.2.5.115 - AR - Add new SP for saving. A new window will popup, and you can stick your SP CREATE script in there - or any other script or query for that matter.
If each developer does this, you'll have a whole list of change scripts, IN ORDER, to run for deployment. If you right click on one, and select Create Command File, VS.NET will create a .cmd file that will run all of these scripts for you. It's a bit tricky, just make sure you tell SQL when you create the script whether you're using Win integration or not - it's kinda funny about not letting you change this after you build the script.
It's IMPORTANT that this is done consistently. These scripts AREN'T transactional, so if one fails, your database could be left in an inconsistent state. Worse - if there's a schema difference between a table you're trying to update on your production server and dev machine, the table could be emptied out for good. I'm speaking from experience here. So when you do this, make SURE you have a staged COPY of the database, exact copy, that you run the script on first to make sure it works - and make sure you backup the database if you want the belt and suspenders approach.
Does this help?
Andrew
It does help some. We are using Visual Studio.NET and SQL 2000.
We are using database projects already, but we have designed them differently that you.
We have a folder for stored procedures that is then broken down into subfolders by module in the application, underneath these subfolders are the stored procedures. When we need to change the stored procedure we check it out, make the change and then check it back in.
We were doing it the way that you described, but found in our application the database project was growing an awful lot and that we were not getting the history information for changes to the stored procedures that we wanted.
I have looked through the API and the schema for the tables in Vault, and I think what I really want to be able to do is to retrieve a distinct list of the database objects that have been modified between two dates or labels and then save them out as files or even better just read them from a stream into a string or something like that. I haven't done enough digging yet to know if that is going to be possible.
Greg
We are using database projects already, but we have designed them differently that you.
We have a folder for stored procedures that is then broken down into subfolders by module in the application, underneath these subfolders are the stored procedures. When we need to change the stored procedure we check it out, make the change and then check it back in.
We were doing it the way that you described, but found in our application the database project was growing an awful lot and that we were not getting the history information for changes to the stored procedures that we wanted.
I have looked through the API and the schema for the tables in Vault, and I think what I really want to be able to do is to retrieve a distinct list of the database objects that have been modified between two dates or labels and then save them out as files or even better just read them from a stream into a string or something like that. I haven't done enough digging yet to know if that is going to be possible.
Greg
-
24Seven
Version control of SQL Server
Both those solutions are, to say the least, a pain in the ass. The nice thing about Vault is that it is integrated into the VS.NET IDE *and* it is easy to use. I'm surprised that SourceGear hasn't designed a decent source code control element that *integrates* with VS.NET *and* the SQL Server objects. I want to be able to view my server explorer (or something similar) and simply check out/in any SQL Server object without having to remember (or more importantly have my developers remember) the magic naming scheme.