Automated Database Build from scripts
-
All our databases (dev, test and UAT) are build using a series of scripts. The scripts include schema, stored procedures, system data and test/demo data.
I am looking for a utility/ sample code to run through a selected set of scripts and execute them against a database. I have tried a database project in Visual Studio 2003 as a mechanism and while promising, way too much information is returned in contrast to what is reqtured by running the same scripts in Query Analyser.
The other aspect I am interested in controlling is to stop certain scripts being run if they have already been run such as data load scripts. I.e some versioning concept. I now how to write the SQL. I just don't how to control the script execution in Query Analyser
-
How about using OSQL to run the scripts?
--------------------
Colt 45 - the original point and click interface
-
We did try osql but found a few problems.
1) that the ouptut was not very helpful, lots of numbers output, presumaby one per statement
2) we create some demo accounts with encrypted passwords. osql corrupts the passwords while wsql does not
3) Seemed slower than running through the scripts sequentially
Having said that, I have some ideas about avoiding some of those problems so it may be worth a relook.
Is there any way to abort a script from within a script if a condition is not met?
-
"abort a script from within a script"
Is this to abort a T-SQL script or, the script that's calling OSQL? If you do a raiserror in the script that's executing you should be able to pickup that error as an OSQL completion code.
From within a T-SQL statement you would use return values from the script that's being executed.
--------------------
Colt 45 - the original point and click interface -
Please look at DB Ghost (http://www.dbghost.com) it has build, comparison and synchronization abilities directly from your source control which is - if I'm not mistaken - exactly what you want and probably most other people as well although they have yet to realize it.
A good article on the subject can also be found on the site: http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
-
Did you try Visual Build? We are using visual build and Visual source safe, in visual build you can create build package for full build as well as incremental builds.
-
for databases? I don't think so.
-
We are using it for database builds for several projects and it works great.
-
how do you handle dependancies?
-
based on the naming conventions. You need to name the script so if you order scripts by name it appears in the same order as you want to execute. We generally name it will datetime stamp in the beginning of the scripts so it runs exactly how it appears. We have also different folders for schema, data, store procedures, views and so on. Order of the folders you can set when you create script for the build and inside those folders it runs as per the naming convention.
-
sounds like high mantenance and prone to error due to the need for human intervention IE: strict rules - very unlike DB Ghost. But good luck to you.
-
Mark
I'm sure that everyone you talk to knows how "GREAT" your program is, but this doesn't mean that other products will work better in a given environment. In the posters environment it might not be "high maintenance and prone to error" and sometimes a little human intervention is a good thing. All this just sounds like someone trying to drum up business and not really addressing the posters questions/concerns.
--------------------
Colt 45 - the original point and click interface -
Thanks for all the feedback
I have played with a lot of the settings for osql and it is now working ok.
I am going to have a look at dbghost as i have looked at other products.
-
Hello Phil Cart,
>I'm sure that everyone you talk to knows how "GREAT" your program is, but this doesn't mean that other products will work
>better in a given environment.
I’m absolutely positive that very few people know how “GREAT” our software is as we are a small company with very little marketing budget and talking to the SQL community through sites like this give us exposure to the SQL community. I’m a SQL programmer/dba myself so I do think about the questions and associate the problems with real life experiences – I like to think that the software solves very real problems and people aren’t aware that such software exists.
I built the original version of the software to solve these very real and common problems as I had them in every shop I ever worked in. I’m no spring chicken either at 35, albeit I’m learning every day.
What I mean by high maintenance is that naming conventions I’ve found are very cumbersome to programmers and dba’s alike as best explained by example: You have a naming convention which governs your build which has to be known by every SQL programmer/dba in your organization as when this is not followed errors and necessary maintenance is incurred. Every new SQL programmer/dba needs to know this convention before they can become productive. The naming convention is logically based and doesn’t reflect the business – I’m a total believer in databases reflecting business entities. For me this is un-necessary and adds complexity to the environment which when small may not seem apparent however most businesses grow and these rules become unwieldy very quickly in my experience.
Wouldn’t a better idea be a file naming convention which reflects the database objects? Wouldn’t this help developers when they need to make changes to files?
What about a piece of software that doesn’t care what naming conventions are used, that doesn’t require you to create an order as it works it out itself? That’s what I call low maintenance, set it up and forget about it – just look at results and do the work that cannot be taken away from humans.
Repeatable processes are what computers combined with software are good for, humans aren’t.
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply