We help IT Professionals succeed at work.

Using Source Safe with SQL Server

346 Views
Last Modified: 2013-11-25
Hello,

We have just installed a Visual Source Safe solution on our server and we are trying to figure out if it's possible to place database objects under source control.  Initial searches say only stored procedures, but it'd be nice if we could also keep track of DB structure changes as well.  Does anyone know if this is possible?

Thanks,

Mike
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Instead of db objects you should store scripts to create/update these objects. The reason for storing this info in VSS is a possibility to create these objects from the scratch to the latest state or to given point which is feasible by SQL scripts.

It means you have to create script for any database structure/definition change, run the script and store it into VSS. Even some lookup and application configuration data may be created by the script.
Tapan PattanaikSenior Engineer

Commented:
hi MClarkCCR,
                         Open Microsoft SQL Server Management Studio the in file menu click Source Control , then click Launch Microsoft Visual Source Safe, then add your files what ever you want.

ie Microsoft SQL Server Management Studio-->File-->Source Control-->Launch Microsoft Visual Source Safe-->( after opening visual Source safe u can add what ever files you want to add)

Thanks.

Author

Commented:
pcelba:

We would like if it could act in a similar way to how we work with websites in visual studio.

We check out files to a local copy of the project on our machines and test changes on a local IIS (not ASP.NET development server).  When we're happy with the changes we check in our files back to the VSS DB.  

We also have a public development server that we use so that clients can view the changes/progress to their site without affecting a live production version.  We deploy from VSS to the public dev only when we've got everything working as expected, so the client doesn't call us in a panic everytime the dev site throws an exception :).

We want the same to hold true for our DB, so that we can check in/checkout database data and structure changes to VSS, without actually modifying the DB that is wired to our public dev version of the site.  Then when we feel it's ready, we will push the DB stored in VSS to the version that the public dev will use.  We essentially want to feel like we can change/break whatever we want without the client knowing and only update what they can see when we're ready.

As far as scripting goes, can the process be automated so that any changes I make to the data/structure on the DB be put into a script, I guess like an iteration?  Or will I just have to rescript the entire database creation evertytime there's a release and then store that script in VSS?

tapanpattanaik:

After I open VSS and create a new project (i.e MyNewDevDB) and choose add files, do I have to go to the files on the DB server and add the .mdf and log file directly?

Thanks so much for your responses,

Mike
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
really should be treating each change to the database as a program. Write the program first, test, put it into source safe, then apply to the real database.

with my site, there are NO changes to the database without a script and each and every script is in source safe, and as each script is run, it updates a central table placing an entry with what it is, the version etc...

sounds a bit stifled but it works like a charm and can always find any script and any version... did have a bit of pushback from the developers at first, but then, every one does it and no complaints.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Oh, forgot one important thing... DDL triggers capture stuff AFTER they have happened - so can sometimes be a case of "yep, that is what killed the machine" rather than preventing nasties before they happen....

Author

Commented:
Well,

That is certainly a lot of information to take in.  I really appreciate your guys' insight to this situation.  As I'm not exactly a DBA by trade a lot of this stuff seems a bit over my head, but I've definitely got some good starting points for researching a solution that could work for our development process.  Our actual process is still in its infancy stage though, as we've previously relied solely on cowboy coding and all the 'perks' that go along with it.  

Thanks for all your help,

Mike
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Mike, it has been a pleasure, and good to be thinking like this whilst still in its infancy stage. Easier to start with a few new rules than to break old bad habits :)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.