Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using Source Safe with SQL Server

Posted on 2009-05-07
9
Medium Priority
?
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 43

Expert Comment

by:pcelba
ID: 24326319
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
ID: 24326393
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
ID: 24326827
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 51

Expert Comment

by:Mark Wills
ID: 24329079
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
 
LVL 43

Accepted Solution

by:
pcelba earned 1000 total points
ID: 24331153
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 1000 total points
ID: 24339626
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
ID: 24339688
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
ID: 24339798
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
ID: 24339963
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

618 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