| Author |
Topic |
|
majkinetor
Yak Posting Veteran
55 Posts |
 Posted - 2008-10-06 : 10:41:49
|
| Hello there.I wonder is there any kind of software or feature that makes me able to control versions of database I am working on.If there are 10 users making changes to table definitions I wont to see in log who did what, revet changes if needed and all other things typical for subversion systems.If not, can you suggest me alternative methods or describe in short where should I look for such information among SQL Server system tables so that I can make such app myselfThanks a lotwww.r-moth.com http://r-moth.deviantart.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-06 : 10:57:49
|
| yup you've such version control systems. Microsoft Visual Source Safe is one such product. Another one is Tortoise SVN. |
 |
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-10-06 : 11:14:05
|
quote:
Originally posted by visakh16
yup you've such version control systems. Microsoft Visual Source Safe is one such product. Another one is Tortoise SVN.
MSVSS is a good tool, and I've used it but it always seems to require discipline for it to work correctly for SQL objects. With application code, it'll place a read-only tag on your file on the file share. For SQL code (stored procs, any DDL) you need to store the script to a file share and only make changes there (discipline) before running it against the database. That does not eliminate the possibilty of altering database objects using any of the client tools, circumventing the scripted code however (and, yes, you can remove the read-only on your application code too, bypassing VSS!). Visakh16, am I missing some within VSS with respect to SQL???Terry |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-06 : 11:43:26
|
quote:
Originally posted by tosscrosby
quote:
Originally posted by visakh16
yup you've such version control systems. Microsoft Visual Source Safe is one such product. Another one is Tortoise SVN.
MSVSS is a good tool, and I've used it but it always seems to require discipline for it to work correctly for SQL objects. With application code, it'll place a read-only tag on your file on the file share. For SQL code (stored procs, any DDL) you need to store the script to a file share and only make changes there (discipline) before running it against the database. That does not eliminate the possibilty of altering database objects using any of the client tools, circumventing the scripted code however (and, yes, you can remove the read-only on your application code too, bypassing VSS!). Visakh16, am I missing some within VSS with respect to SQL???Terry
Sorry Terry i didnt get what you told about VSS. we maintain all out SQL code in VSS and use it for adding/reverting versions. |
|
|
|
majkinetor
Yak Posting Veteran
55 Posts |
Posted - 2008-10-06 : 12:28:10
|
| I don't quite follow you guys ...I already use Tortoise SVN among some other SVN clients like Ankh SVN for Visual Studio.But how can I control database changes via it ?I want to put database definition itself in a repo so if I, lets say, add column to table X i can see that in log history and eventualy revert changes.The problem is that in normal SVN usage you must CheckIn/Out so you work on local copy while in this case DataBase exists on only 1 place, specific SQL Server and many users change it.That must be some kind of specialised repositorium.www.r-moth.com http://r-moth.deviantart.com |
|
|
|
majkinetor
Yak Posting Veteran
55 Posts |
Posted - 2008-10-06 : 12:34:29
|
| In other words there is no local copy.Now, I see from above that tosscrosby is mentioning something like saving versions of stored procedures and other sql objects which is what I need, but how do I set that up ?Specifically, I want to open table definition in any SQL client, add table to database, and then be able to undo that operation via repositorium. THe sql user that connected to database should be reported in the project history as the one that altered database.www.r-moth.com http://r-moth.deviantart.com |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-06 : 12:41:46
|
quote:
Originally posted by majkinetor
In other words there is no local copy.Now, I see from above that tosscrosby is mentioning something like saving versions of stored procedures and other sql objects which is what I need, but how do I set that up ?Specifically, I want to open table definition in any SQL client, add table to database, and then be able to undo that operation via repositorium. THe sql user that connected to database should be reported in the project history as the one that altered database.www.r-moth.com http://r-moth.deviantart.com
the repository just provides a mechanism where you can store the copies of versions of a particular SQL code. it dont have ability to apply the sql code to database. the code still needs to be applied manually or you need to write batch scripts to take code from repository and apply it to appropriate database (our db guys have done that). But by making policy by which all code changes are released only via checkins through SVN/VSS, you can at any time quickly track what was last change done, who did it and even audit entire sequence of code changes and may even revert to earlier code. |
|
|
|
majkinetor
Yak Posting Veteran
55 Posts |
Posted - 2008-10-06 : 13:26:26
|
| That means that I can't use visual tools to change database at all, but only change it via sql scripts. Ofcourse tools can do that for you, I don't know if SQL Server Manager itself has that option while u visualy edit database - I edit database only via Visual Studio Addin or some portable db tool so it doesn't require instalation - but for instance if I edit something in Gipo Db DB Utilities(freeware, portable) i can always press button "View as SQL" to see changes I did as script. I wouldn't like to think about it, anyway, the same way I don't think about it when I keep my Visual Studio Solution in repository. I just work with it like it doesn't exist. visakh16, how do you edit such database ? Say you want to edit Table via that system your db guys did. How do you remove column ? Editing appropriate sql file directly then CheckingOut that file to normal Repo or u can use some visual tool or something else.... I have a team of X people that need to adopt database versioning as transparent as possible.The only thing that I can think about is to create application that will scan database quite often and record changes. Depending on frequency of scan some intermidiate work could be lost but if you set it to be done every 10 seconds for instance it will be OK. It would be ofc done only on test database due to the high frequency of querying.www.r-moth.com http://r-moth.deviantart.com |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-10-07 : 09:42:12
|
| Visakh, what I was trying to say, as a versioning tool, it's obviously only as good as those that use it. For application code, if the working folder is on a file share (for all your developers to access), it'll change the file to read-only when you check it in, essentially letting the developers know it's in VSS and should be checked out for modifications. It does not stop them from changing the attributes of the actual file on the share, modify the code and circumvent VSS all together. My questions (statement?) regarding SQL objects, is that you have to script the stored proc (or whatever) and check it in but doing so does not place any kind of marker on the object within SQL that I'm aware of. So again, you can circumvent VSS by using EM or QA to modify the object without touching what was checked into VSS, basically breaking the version control.Terry |
|
|
|
|
Database Repo