1

We are bringing our db under version control. We have around 2400 object out of which most are stored procedures.

So we have made drop if does not exist create scripts for all our objects except tables.

We are maintaining the table scripts in a separate folder in which the delta scripts are stored. Only the delta scripts are numbered. Other scripts relating to procedures, views and functions are to be run directly.

So when the developer commits the change in the svn we want to run them on a staging release. The problem is the dependency of objects.

If a developer commits several scripts in one commit which may be dependent on each other or if we want to merge several revisions and then run scripts combined then we are not able to determine the correct order of running the scripts.

Can anyone suggest some good way to manage the dependency and help us to make sure the scripts run the first time itself.

We can not use redgate tools in so many of our production servers so I would appreciate some other alternative than using sql compare or sql sourcecontrol.

We would always run the table delta scripts first since they would not be dependent on other objects (the dev would have also managed it in the script) and then the other objects

We have come out with a few workarounds for sequence of running other objects like

1) We can store the dependency of the objects in another file with same name but different extension so that if the dependency changes then the developer should commit it with the script. We can then make a tree according to which we can run the script.

2) We can create a tree first then commit it in the svn if the dependency changes then we can add it in the tree after the scripts are tested on the staging machine and error occurs.(This would require a svn commit after the first fail and requires that the first time the automated process fails)

3) We can run the scripts again and again without using transactions a maximum of the number times as is the number of objects. This would lead to running of all the scripts correctly at the end assuming the worst case scenario.

If you have some other ideas or can improve some of these ideas then please contribute.

puneet
  • 769
  • 1
  • 9
  • 34

3 Answers3

0

Have you looked at SQL Server Management Objects?

It has support for identifying relationships via the DependencyWalker class and so may be just what you're after.

Whether you want to trust that the dependency chains maintained by SQL Server are accurate is another thing entirely though... If not, then your best bet may be to drop the automation and simply have your developers responsible for committing an appropriate upgrade script, that you would then replay in the order committed to SVN.

chrisb
  • 2,200
  • 1
  • 20
  • 23
  • This would be able to make a dependency tree only after the scripts are in the db. I want to know beforehand running on db what the dependency will be so that the scripts do not throw errors. – puneet Feb 07 '13 at 14:42
  • Surely your developers test their changes on a development/local server? I've used an SMO-based tool before to take snapshots of whole databases so I'm certain you could write one that scripted and wrote the changed objects to source control, including the dependency chains. – chrisb Feb 07 '13 at 15:03
0

Have you looked at this?

http://www.red-gate.com/products/sql.../sql-source-control/

It's a pretty decent set of tool for capturing your differences with minimal effort from yourselves.

Paddy
  • 33,309
  • 15
  • 79
  • 114
0

If you can't use any other kind of automation, you can probably use tsort. The tsort utility is a Unix program that implements a topological sort. (It's probably available for most operating systems.) It outputs a full, total ordering of strings based on input that shows partial ordering.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • If we do not have the access to DB always then we can create a sorted list of all the objects already present and use it to order the files. – puneet Feb 13 '13 at 14:09