Solved

Using Source Safe with SQL Server

Posted on 2009-05-07
9
297 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
0
Comment
Question by:MClarkCCR
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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.
0
 
LVL 21

Expert Comment

by:Tapan Pattanaik
Comment Utility
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.
0
 
LVL 3

Author Comment

by:MClarkCCR
Comment Utility
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
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 41

Accepted Solution

by:
pcelba earned 250 total points
Comment Utility
I agree the fact each change must be scripted. Then the question is if it is necessary to store scripts in VSS because while the script is finalised and executed it should not be changed any more and all additional changes require another script... OK, scripts stored in VSS are better versioned at least.

The development work on database objects is rather different from application development. To allow pure local development you would need local copy of the database also. I don't know what data are you storing/processing in your database but they are obviously not suitable for versioning in VSS. Database backup is the most common method for versioning and latest structures conservation. Scripts or bulk exports for some lookup and initial data are the only exceptions for versioning in VSS.

Scripts are obviously "incremental" and they always should check if they were executed already to avoid their duplicate implementation. To script the whole database is not necessary because this work can be done by all incremental scripts application (in proper order, of course).

Your public dev is obvious solution for large applications. Then you need internal development database where you apply finished changes from all team members and local (temporary) databases for some long tasks which could block others from their work on internal development.

Automated scripting is very good idea and you maight program it yourself for incremental scripts creation. But it is not necessary because each change in your development should start with script creation, so all scripts are prepared by developers in advance. To script the whole database is easier but you obviously don't need such scripts. They are useless for production data because they destroy existing data obviously.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
Comment Utility
Versioning, date time, user, are all important aspects and reasons why VSS is important, and being able to rollback / retrieve / inspect a specific version is important.

If you allow changes directly to the dtabase, then it can get cumbersome to control. Some procedures and changes can involve a few attempts and really should be regarded a development task as per any development. Just because it facilitates instant and immediate change does not mean it can bypass all the controls we know and trust in a classic development task.

We insist on specific formatting of any script. It must contain a specific layout and documentation up the top. The database developers can test and change on a local instance of a database, upgrade to a common dev database for more integrated testing, and is then ready for QA. The QA process includes looking at the compliance of the script, running the script (we have written a stored procedure to facilitate and check and log the script), and the results tested. When handed over to QA, it essentially stops being a development project per se and is now part of the QA and acceptance testing space.

Part of the development plan is to know why the script is being generated and the expected results. part of the QA process is to ensure it runs and then all a testing stage to begin. Part of the QA environment is having an appropriate representative, reliable and predictable database as a basis. Once approved in QA, it can then be 'packaged' or scheduled for release to "live" databases.

Sounds like a lot of work, but set the environment and the processes and it will work with very few and far between surprises.

Having said that, you can run DDL triggers, while it may be more automated, it is harder to track and trace...


-- create a log table to record all DDL events
 

CREATE TABLE ddl_log (ChangedOn datetime, ChangedBy nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));

GO
 

-- create a DDL trigger on this database
 

CREATE TRIGGER log ON DATABASE 

FOR DDL_DATABASE_LEVEL_EVENTS 

AS

   DECLARE @data XML

   SET @data = EVENTDATA()

   INSERT ddl_log (ChangedOn, ChangedBy, Event, TSQL) 

   VALUES (GETDATE(), CONVERT(nvarchar(100), CURRENT_USER), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;

GO
 
 

--Test the trigger

CREATE TABLE TestTable (a int);

go
 

ALTER TABLE testtable add b varchar(100);

go
 

CREATE VIEW vw_testtable as

SELECT * from testtable

go
 

ALTER view vw_testtable as

select a,b from testtable

go
 

CREATE procedure usp_get_testtable(@a int)

as

begin

   select * from testtable where a = isnull(@a,a)

end

go
 

-- now tidy up example DDL activities
 

DROP VIEW vw_testtable;

DROP procedure usp_get_testtable;

DROP TABLE TestTable ;

GO
 

-- now let us look at what was logged...
 

SELECT * FROM ddl_log ;

GO
 

-- finish tidy up of our testing...
 

DROP TRIGGER log ON DATABASE

GO
 

DROP Table ddl_log 

GO

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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....
0
 
LVL 3

Author Comment

by:MClarkCCR
Comment Utility
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
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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 :)
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now