I have a database on my test machine that will need to be installed on users
machines. I would like to create the database with the given schema on the
users machine and also with some suitable default values in the tables. I
note that although I can script the schema so that re-creating the structure
of the database is simple on the users machine, I cannot script the contents
of the tables also (automatically). What I would like to do is take some
kind of "snapshot", save it as a script and then run this script in my
installer. Are there any tools available to do this?
Secondly and related to the above: if I subsequently make changes to the
database schema (adding or removing columns, altering, adding or removing
stored procedures etc.), how do I roll out those changes to a customer? Do
I need to hand code an "upgrade" script, or is there a tool that will
produce a "difference between" script I can run on the customers machine?
Thanks for any tips you can give me about this.
RobinRobin Tucker (idontwanttobespammedanymore@.reallyidont.com) writes:
> I have a database on my test machine that will need to be installed on
> users machines. I would like to create the database with the given
> schema on the users machine and also with some suitable default values
> in the tables. I note that although I can script the schema so that
> re-creating the structure of the database is simple on the users
> machine, I cannot script the contents of the tables also
> (automatically). What I would like to do is take some kind of
> "snapshot", save it as a script and then run this script in my
> installer. Are there any tools available to do this?
SQL Server MVP Vyas Kondreddi has a tool that generates INSERT statements
from a table: http://vyaskn.tripod.com/code.htm#inserts
> Secondly and related to the above: if I subsequently make changes to the
> database schema (adding or removing columns, altering, adding or
> removing stored procedures etc.), how do I roll out those changes to a
> customer? Do I need to hand code an "upgrade" script, or is there a
> tool that will produce a "difference between" script I can run on the
> customers machine?
There are several paths to take. Many people use a third-party tool that
compares two databases and then generates a script. Very popular is
SQL Compare from Red Gate. I have not used this tool myself, though.
A better approach in my opinion, is to have all code under source control.
In this case, your development database is not your master, but the
version-control system is. A basic version-control system will not
provide any update scripts for you, as a version-control system is a
general container for all sorts of code. What you do is that when you
ship, you set a label, and then you can later inquire the VCS for
changes since that label. There are plenty of VCS on the market. Very
popular among Microsoft customers is Visual SourceSafe which is part
of Visual Studio. VSS is not a very good for serious configuraton
management, but it's easy to get started with, and works perfectly OK for
smaller groups.
There are a couple of third-party tools that are specialized for doing
version control on SQL Server. Typically, they sit on top of SourceSafe
or some other generic VCS. Unfortunately, I don't recall any names right
now.
In our shop we use VSS, together with a toolset that includes tools for
loading stored procedures (with a lot of bells and whistles, like
automatic insert of SET NOCOUNT ON, WITH ENCRYPTION (on request),
automatic GRANT, a preprocessor). We also have a tool that builds
update scripts from the checkins in SourceSafe. The tool also maintains
its own tables in the target databases, so that we know in which state
each database is in. This toolset is available as freeware on
http://www.abaris.se/abaperls/. (I uploaded the latest version of it,
just the other day, by the way.)
Judging from your question, you may be best off with something like
SQL Compare in the short run. But if more people get involved with
your work, you should definitely consider to move to a version-
control system. There are a few things to keep in mind with updating
from a model database:
o A development may contain junk code and junk tables from tests and
experiments.
o A table change may be as simple as adding a nullable column, but it
can also be very complex if you are making a major restructiring. A
tool probably needs some help in this case.
o If you have preloaded data that you want to deploy, you need a tool
where you selectively can migrate data.
The way we handle pre-loaded data by the way, is to enter the data in
Excel books, and then we have a tool that generates INSERT files from
the the Excel files. The files does not contain any INSERT statements,
but calls to stored procedures which inserts or updates. The reason we
use Excel is that some of our files are quite complex. and we have
different settings for different customers.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Very interesting. Thanks for your comprehensive reply. I think I will need
to study this question at some length before deciding which approach to
take.
Robin
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9595F230624F5Yazorman@.127.0.0.1...
> Robin Tucker (idontwanttobespammedanymore@.reallyidont.com) writes:
>> I have a database on my test machine that will need to be installed on
>> users machines. I would like to create the database with the given
>> schema on the users machine and also with some suitable default values
>> in the tables. I note that although I can script the schema so that
>> re-creating the structure of the database is simple on the users
>> machine, I cannot script the contents of the tables also
>> (automatically). What I would like to do is take some kind of
>> "snapshot", save it as a script and then run this script in my
>> installer. Are there any tools available to do this?
> SQL Server MVP Vyas Kondreddi has a tool that generates INSERT statements
> from a table: http://vyaskn.tripod.com/code.htm#inserts
>> Secondly and related to the above: if I subsequently make changes to the
>> database schema (adding or removing columns, altering, adding or
>> removing stored procedures etc.), how do I roll out those changes to a
>> customer? Do I need to hand code an "upgrade" script, or is there a
>> tool that will produce a "difference between" script I can run on the
>> customers machine?
> There are several paths to take. Many people use a third-party tool that
> compares two databases and then generates a script. Very popular is
> SQL Compare from Red Gate. I have not used this tool myself, though.
> A better approach in my opinion, is to have all code under source control.
> In this case, your development database is not your master, but the
> version-control system is. A basic version-control system will not
> provide any update scripts for you, as a version-control system is a
> general container for all sorts of code. What you do is that when you
> ship, you set a label, and then you can later inquire the VCS for
> changes since that label. There are plenty of VCS on the market. Very
> popular among Microsoft customers is Visual SourceSafe which is part
> of Visual Studio. VSS is not a very good for serious configuraton
> management, but it's easy to get started with, and works perfectly OK for
> smaller groups.
> There are a couple of third-party tools that are specialized for doing
> version control on SQL Server. Typically, they sit on top of SourceSafe
> or some other generic VCS. Unfortunately, I don't recall any names right
> now.
> In our shop we use VSS, together with a toolset that includes tools for
> loading stored procedures (with a lot of bells and whistles, like
> automatic insert of SET NOCOUNT ON, WITH ENCRYPTION (on request),
> automatic GRANT, a preprocessor). We also have a tool that builds
> update scripts from the checkins in SourceSafe. The tool also maintains
> its own tables in the target databases, so that we know in which state
> each database is in. This toolset is available as freeware on
> http://www.abaris.se/abaperls/. (I uploaded the latest version of it,
> just the other day, by the way.)
> Judging from your question, you may be best off with something like
> SQL Compare in the short run. But if more people get involved with
> your work, you should definitely consider to move to a version-
> control system. There are a few things to keep in mind with updating
> from a model database:
> o A development may contain junk code and junk tables from tests and
> experiments.
> o A table change may be as simple as adding a nullable column, but it
> can also be very complex if you are making a major restructiring. A
> tool probably needs some help in this case.
> o If you have preloaded data that you want to deploy, you need a tool
> where you selectively can migrate data.
> The way we handle pre-loaded data by the way, is to enter the data in
> Excel books, and then we have a tool that generates INSERT files from
> the the Excel files. The files does not contain any INSERT statements,
> but calls to stored procedures which inserts or updates. The reason we
> use Excel is that some of our files are quite complex. and we have
> different settings for different customers.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I would recommend you have a look at Innovartis DB Ghost at
http://www.innovartis.co.uk/ It is designed specifically for your
requirement i.e. automated database change management, generation of
upgrade scripts, build verification, database synchronization,
deployment of changes, integration with version control system, handles
schema & data.
John McGrath
SQL Server DBA MCSE
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql
