Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Database objects & version control- Best practices? Expand / Collapse
Author
Message
Posted Tuesday, February 17, 2009 5:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 8, 2011 2:23 AM
Points: 36, Visits: 103
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
Post #658389
Posted Tuesday, February 17, 2009 5:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 12,876, Visits: 31,786
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #658408
Posted Tuesday, February 17, 2009 7:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 11,148, Visits: 12,888
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #658494
Posted Wednesday, February 18, 2009 2:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 8, 2011 2:23 AM
Points: 36, Visits: 103
Thanks for that, we seem to have missed the part about scripting each object to a single file.
Post #659186
Posted Wednesday, February 18, 2009 2:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 8, 2011 2:23 AM
Points: 36, Visits: 103
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
Post #659187
Posted Wednesday, February 18, 2009 3:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 23, 2010 8:30 AM
Points: 229, Visits: 146
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
Post #659188
Posted Wednesday, February 18, 2009 3:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 17, 2014 7:18 AM
Points: 2,439, Visits: 6,426
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.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #659201
Posted Wednesday, February 18, 2009 6:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 11,148, Visits: 12,888
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #659289
Posted Friday, March 6, 2009 10:24 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:30 AM
Points: 938, Visits: 1,734
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.
Post #670447
Posted Monday, April 20, 2009 11:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 29, 2009 8:36 AM
Points: 13, Visits: 84
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.
Post #700815
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse