Database objects & version control- Best practices?
-
We operate a SQL 2005 database and want to keep a version control history of the objects (views, stored procedures, functions etc.) held within it using Visual SourceSafe (VSS). We have upgraded to SQL 2005 from SQL 2000 and our version control process hasn’t changed too much
Now when we make a change to a view or stored procedure we would apply the changes to both our SQL 2005 live server and our SQL 2000 server. We then open up SQL 2000 Enterprise Manager and use the ‘All Tasks’ – ‘Generate SQL Script’ – checking the ‘Create one file per object’ to create a SQL script for the object(s) we’ve changed and then open up VSS to check those changes in
This seems like a laborious process and is reliant upon us having a SQL 2000 database to make the exporting of the SQL a bit easier. We can’t believe that we are carrying out the most efficient process here but we can’t see an obvious version control technique in SQL 2005 that is any easier for us. There doesn’t seem to be a facility in SQL 2005 to generate SQL scripts in individual files per object to integrate with VSS in a similar manner
Are we using an antiquated technique for version control for database objects which has been superseded and haven’t realised it? If there isn’t a good technique using SQL Server 2005 Management Studio to integrate with VSS are there any 3rd party tools that carry out this task in an easy fashion?
Regards
Dave
-
when i use that script wizard in 2005 SSMS, i get an option at the end to script per file:
but i'll be darned if i see the equivilent in Tools>>Options Scripting. I think it's only available when the wizard is run.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I have not used it it yet, but RedGate offers some of this functionality integrated with SQLCompare using SQLChangeset. This integrates with SourceSafe.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Thanks for that, we seem to have missed the part about scripting each object to a single file.
-
Jack Corbett (2/17/2009)
I have not used it it yet, but RedGate offers some of this functionality integrated with SQLCompare using SQLChangeset. This integrates with SourceSafe.We currently use a number of RedGate's products but I confess we had not looked into "SQL Changeset". Just had a quick look and it
seems to offer an alternative method of using VSS, but it's still file based.
I guess we are looking for a tool that will work directly with the objects in the SQL server database without the
need for them to exists as files being checked in checked out.
What sort of procedures do other people use?
Dave
-
Interesting topic - we have a similar problem with version control.
I've just had a look at the SQL Changeset and it doesn't give much benefit over just using vanilla VSS - the objects have to be scripted out of the database into individual files and then imported into VSS
Isn't there anything that integrates directly with Sql Server 2005 Management Studio and VSS together to manage versions of database objects (tables/views/functions/stored procedures etc.)?
Tony
-
I'm not 100 percent sure, but I vaguely remember reading that Visual Studio Team Edition for Database Professionals allows you to extract an entire schema and store it in TFS.
-
I have not used either one of these, although I vaguely remember investigating one a few years ago, but I think at least the first one may do what you are looking for:
http://www.skilledsoftware.com/sqlsourcecontrol.htm
http://www.bestsofttool.com/SQLSourceSafe/SSS_Introduction.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
I maybe missing something here, but if you have the objects in VSS (Visual Source Safe) already then you should just be checking out the file version from VSS then editing it in SSMS then checking it back in. In fact, SSMS 2005 did a much better version of source control integration that in SQL Server 2000 toolset. You can even create solutions and project containers for your files. The way I currently do development of an object which is under VSS control is check it out from VSS, then open the file which contains the script. The SSMS interface will show the file name in the tab and should have a red checkmark indicating I have a checked out version of a file. (If I just did a "Get Latest Version" and didn't have it checked out it would have a padlock graphic on the right side of the tab.) After making my changes, I save the file then I check it back in through the VSS interface. If you have the files included in a project and solution, then you can just check them in and out from the SSMS interface. But, I have found when working on an individual file from source control I have to do the check-in/check-out from Visual SourceSafe.
One thing to be aware of when working with the database objects, as we know they are stored on the database, so if everyone isn't following the procedure of only editing the objects from the source control files it is possible that the file in source control may become out of sync with what is on the server. Another item to consider, the file is created the way you wrote it. For example, if you saved the file as a "CREATE PROCEDURE" the source control file will be saved as "CREATE PROCEDURE" which means you may have to include the "DROP PROCEDURE" statements in you file if you want to go that route or you just have the policy that the first change to a previously created procedure changes the file to an "ALTER PROCEDURE". So, as a development group you would have to decide on your development procedures. It does seem like you are doing duplicate work based on your initial explanation and if the 2000 instance is just for the generate script functionality then you can get rid of that part of the process.
Another consideration as mentioned by an earlier post is Visual Studio Team System Database Edition. It is more expensive solution but it is supposed to be a development suite for the Database Developers. The company I work for has purchased it recently and I am on a team to roll it out to our developers so I am still learning it myself. The software supposedly handles the building of the proper scripts for making changes to database objects. For instance, if an object already exists, it will use an ALTER statement rather than a CREATE statement. It has tools that compare the schemas between your changes and what exists currently. It also provides data generation tools and unit testing. But as I said, I'm still learning how to use the toolset and determining how they fit into our development environment. Here is the link to the blog of new DataDude from Microsoft: DataDude
I hope that helps.
-
Sorry that I replay for a post from March, but just wanted to add you can do SQL Version control with 3rd party tool, and RG is not the only one in the market. Its much easier, especially for the new users and people that doesn't have the time to do it and looking for fast, easy and safe way for version control.
--------
SQL Data compare , Synchronize data from diffrent surces, and many other SQL tools[/url].
-
Are there any freeware tools that help to manage database version?
-
Hi John,
You have hit the nail on the head when you are discussing Visual SourceSafe and SSMS - if you are to use the default integration then you have to edit files within a project basically outside of the database (although within the SSMS editor).
You rightly say that there is no link or constraint between the actual object within the database and the files in the project, they are simply external scripts that affect database objects. This means that people can make modifications to the database objects without having to update the version of the file in Visual Sourcesafe. The database won't even complain that changes are being made outside of the version control.
We want to have a tool that will prevent people changing database objects without first updating the version control system. This projects feature within SSMS is an alternative to the technique we use of scripting the objects to a file in a folder and then adding them into sourcesafe. On the downside though as you mention the interchangability of 'CREATE PROCEDURE....' and 'ALTER PROCEDURE...' can give false changes within a module, whereas using the scripting tool within the database gives a more consistent script.
itamar has mentioned a tool I've never heard of before - I think we will be looking at that to see whether it does what we want - or at least does a better job than what we are managing with at the moment.
Tony
-
I've just read through this thread, hoping, hoping, that someone had found a 'great' (or workable) solution. Alas, it isn't so. I'll add, though, that in the VS6 days source control was integrated, truly integrated into the interface such that when opening up an SP, its contents were checked against the VSS version and the user was warned if different.
Now, I wonder if a solution for storing changes might lay with DDL triggers firing off a CLR assembly that interfaces the VSS API. Too bad my skill-level isn't good enough, and time is too short. But: maybe someone can make their million?
Cheers,
Mark
Mark
Just a cog in the wheel. -
Take a look at message 474053 which describes our method.
http://www.sqlservercentral.com/Forums/FindPost474053.aspx
I describe how we store each object as a separate file in the source code control system (currently Perforce, but Visual SourceSafe before), address the CREATE/ALTER/DROP issue with stored procedures, etc. and guarantee that the upgrade script is consistent.
Not all of the tools will generate a proper "upgrade" script.
Examples:
In our last upgrade we needed to change an XML schema collection. This required that we properly "unwind" any bindings, make the change, and then rebind the XML schema to the various objects. An interesting sequence of operations that I doubt any software tool will do.
Often, as part of a schema change we need to change some meta data values that are stored in tables.
So if the tool will not generate a complete upgrade script and you still have to make manual changes to the script, then the tool hasn't provided any benefit.
[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font] -
May be you know this utility for your source control http://www.dynamsoft.com/Downloads/SAW_Download.aspx
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply