SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database objects & version control- Best practices?


Database objects & version control- Best practices?

Author
Message
david.gray
david.gray
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28046 Visits: 39926
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!

Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18362 Visits: 14893
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
david.gray
david.gray
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 103
Thanks for that, we seem to have missed the part about scripting each object to a single file.
david.gray
david.gray
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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
tony.sawyer
tony.sawyer
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 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
Jan Van der Eecken
Jan Van der Eecken
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2430 Visits: 6494
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)
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18362 Visits: 14893
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
John Dempsey
John Dempsey
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1035 Visits: 1769
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.
itamar-863608
itamar-863608
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search