Click here to monitor SSC
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
sql.queries
sql.queries
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 560
Are there any freeware tools that help to manage database version?
tony.sawyer
tony.sawyer
SSC Veteran
SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)

Group: General Forum Members
Points: 233 Visits: 146
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
starunit
starunit
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 2566
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.
Mauve
Mauve
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1246 Visits: 2049
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.


(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.
manolomalaga
manolomalaga
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 172
May be you know this utility for your source control http://www.dynamsoft.com/Downloads/SAW_Download.aspx
Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3280 Visits: 5160
How about Visual Studio Team Suite 2005 / 2008...?
I have tried Visual Studio Team Suite 2008, and it is very handy for the SQL developers.


BUT Its Not Free...

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here


Jim Russell-390299
Jim Russell-390299
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 1403
I use a poor (simple?) man's solution that runs a sweep of all data base objects nightly, and captures a copy of anything that has been modified in a table.
Works fine for me. I miss multiple changes made during the day, but those are only failed test attempts that are not worth saving anyway. Other than that, I have a complete history of all versions.
manolomalaga
manolomalaga
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 172
You can try VS TeamSystem with this VPC http://www.microsoft.com/visualstudio/en-us/products/teamsystem/default.mspx. Has a web interface very simple. It's looks like Share Point.
itamar-863608
itamar-863608
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 84
tony.sawyer (4/21/2009)
Hi John,

....

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'll be happy to hear what you think of it.

--------

SQL Data compare , Synchronize data from diffrent surces, and many other SQL tools.
mannulalj
mannulalj
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 6
Hi

We use SQL Server 2012 and have stored procedures in Dev and Live environment. When we decide to move changes made to Dev TO Live, we need to be able to compare the scripts in Dev and Live and see the changes so that we can analyse the impact of the same and plan accordingly. We use the Generate Scripts option in both environments, however, the scripts do not come in the same order in each of them and hence it becomes very difficult to compare them. Would you be able to suggest a tool which can give the changes in the same order so that taking a DIFF of them would be easy ? OR will you be able to share something you have developed to know the list of changes made to all Stored procedures in a given range of dates ?

Regards
Mannulal
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