Greetings,
I am in the process of installing a SQL database at a customer
location. I have determined that there are 3 ways to do this, and I
wanted to know which is the best of the 3.
1 Install From Script.
In this method I create the database and its objects in scripts that
are run via osql utility on the SQL server machine. For loading any
initial data that I need in the database I also run bcp commands.
2 Install from a backup
In this method I created an empty database on the SQL server, and then
restore over it the database from a backup of the database that I need
to deploy. Then I add or re-attach the users for the database. I
perform all of these operations using osql as well.
3 Install by attaching the data files.
In this method I created an empty database on the SQL server, and then
I attach the data files to the database using the sp_attach procedure.
Then I add or re-attach the users for the database. I perform all of
these operations using osql as well.
Although it is no problem for me to use any of these methods, I wanted
to know from you veterans out there what the best practices are. And
also if there are any unseen hazards for each method above. Or if I
am totally off-the-target, and there is another method that is the
preferred way.
Thanks in Advance,
roger"great_googley_moogley" <pow_pow1476@.yahoo.com> wrote in message
news:dda0ee86.0404021123.46fd0e4e@.posting.google.c om...
> Greetings,
> I am in the process of installing a SQL database at a customer
> location. I have determined that there are 3 ways to do this, and I
> wanted to know which is the best of the 3.
> 1 Install From Script.
> In this method I create the database and its objects in scripts that
> are run via osql utility on the SQL server machine. For loading any
> initial data that I need in the database I also run bcp commands.
> 2 Install from a backup
> In this method I created an empty database on the SQL server, and then
> restore over it the database from a backup of the database that I need
> to deploy. Then I add or re-attach the users for the database. I
> perform all of these operations using osql as well.
> 3 Install by attaching the data files.
> In this method I created an empty database on the SQL server, and then
> I attach the data files to the database using the sp_attach procedure.
> Then I add or re-attach the users for the database. I perform all of
> these operations using osql as well.
> Although it is no problem for me to use any of these methods, I wanted
> to know from you veterans out there what the best practices are. And
> also if there are any unseen hazards for each method above. Or if I
> am totally off-the-target, and there is another method that is the
> preferred way.
> Thanks in Advance,
> roger
All three options can give the same results, as you say, but there are some
differences in functionality.
Option 1 is good if you need to load different data for different clients -
with other methods you'd need a copy of the database for every possible data
set you need to provide, but with scripts the object scripts are the same,
and only the data (BCP) files change. Or from the database object
perspective, you can provide different subsets and/or versions of stored
procedures etc. to different clients. In addition, this option doesn't
require you to have any access to the filesystem, which can be useful in
environments with limited bandwidth or tight security requirements. The
downside - if you consider it one - is that you need to manage the scripts,
but presumably you're using some sort of source control and deployment
scripting already.
Options 2 and 3 are essentially the same, in that you're creating the
database from files which you need to copy to a filesystem first. The major
difference is that you can restore from a network drive, but you can't
attach a database on a network drive (usually). So if you have no access to
the server's local filesystem, but you do have access to a network share
accessible to the server, you could only use the restore option. The big
advantage of these options is obviously simplicity..
Personally, I would consider option 1 the best in the sense that it's the
most flexible - you have complete control over which objects and data are
loaded, and the layout of the files and filegroups can be decided together
with the DBA before you create the database. But if you're always installing
identical databases in very similar environments (eg. inside a large
company), then the simplicity of one of the other options is likely to be
better for you - less time and effort required.
Simon|||On Fri, 2 Apr 2004 22:07:49 +0200, Simon Hayes <sql@.hayes.ch> wrote:
> "great_googley_moogley" <pow_pow1476@.yahoo.com> wrote in message
> news:dda0ee86.0404021123.46fd0e4e@.posting.google.c om...
>> Greetings,
>>
>> I am in the process of installing a SQL database at a customer
>> location. I have determined that there are 3 ways to do this, and I
>> wanted to know which is the best of the 3.
>>
>> 1 Install From Script.
>> In this method I create the database and its objects in scripts that
>> are run via osql utility on the SQL server machine. For loading any
>> initial data that I need in the database I also run bcp commands.
>>
>> 2 Install from a backup
>> In this method I created an empty database on the SQL server, and then
>> restore over it the database from a backup of the database that I need
>> to deploy. Then I add or re-attach the users for the database. I
>> perform all of these operations using osql as well.
>>
>> 3 Install by attaching the data files.
>> In this method I created an empty database on the SQL server, and then
>> I attach the data files to the database using the sp_attach procedure.
>> Then I add or re-attach the users for the database. I perform all of
>> these operations using osql as well.
>>
>> Although it is no problem for me to use any of these methods, I wanted
>> to know from you veterans out there what the best practices are. And
>> also if there are any unseen hazards for each method above. Or if I
>> am totally off-the-target, and there is another method that is the
>> preferred way.
>>
>> Thanks in Advance,
>> roger
> All three options can give the same results, as you say, but there are
> some
> differences in functionality.
> Option 1 is good if you need to load different data for different
> clients -
> with other methods you'd need a copy of the database for every possible
> data
> set you need to provide, but with scripts the object scripts are the
> same,
> and only the data (BCP) files change. Or from the database object
> perspective, you can provide different subsets and/or versions of stored
> procedures etc. to different clients. In addition, this option doesn't
> require you to have any access to the filesystem, which can be useful in
> environments with limited bandwidth or tight security requirements. The
> downside - if you consider it one - is that you need to manage the
> scripts,
> but presumably you're using some sort of source control and deployment
> scripting already.
> Options 2 and 3 are essentially the same, in that you're creating the
> database from files which you need to copy to a filesystem first. The
> major
> difference is that you can restore from a network drive, but you can't
> attach a database on a network drive (usually). So if you have no access
> to
> the server's local filesystem, but you do have access to a network share
> accessible to the server, you could only use the restore option. The big
> advantage of these options is obviously simplicity..
> Personally, I would consider option 1 the best in the sense that it's the
> most flexible - you have complete control over which objects and data are
> loaded, and the layout of the files and filegroups can be decided
> together
> with the DBA before you create the database. But if you're always
> installing
> identical databases in very similar environments (eg. inside a large
> company), then the simplicity of one of the other options is likely to be
> better for you - less time and effort required.
> Simon
I am using option 1 too. But there is one thing I cannot do with it. This
thing is installing custom dll implementing extended stored procedure into
remote computer. Do you know any solution for this problem?
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||great_googley_moogley (pow_pow1476@.yahoo.com) writes:
> 1 Install From Script.
> In this method I create the database and its objects in scripts that
> are run via osql utility on the SQL server machine. For loading any
> initial data that I need in the database I also run bcp commands.
Be careful to use the -I option to enable the option QUOTED_IDENTIFIERS.
This is paricularly important if you are using indexed views.
> 2 Install from a backup
> In this method I created an empty database on the SQL server, and then
> restore over it the database from a backup of the database that I need
> to deploy. Then I add or re-attach the users for the database. I
> perform all of these operations using osql as well.
> 3 Install by attaching the data files.
> In this method I created an empty database on the SQL server, and then
> I attach the data files to the database using the sp_attach procedure.
> Then I add or re-attach the users for the database. I perform all of
> these operations using osql as well.
This far, all methods may appear to be the same. But then comes the next
step: you need to install fixes, updates and changes. Suddenly option
1 is the only one possible.
In our shop we ship install kits both for installation of new databases,
and upgrades. We have a toolset that includes a tool building an empty
database from SourceSafe. You can do that do step, so that you first
extract the files, and then build from the disk at the remote script.
The toolset also includes a tool that can build update scripts with
changed files, and the same applies to them that you can run in two steps.
If you're curious, it's available at http://www.abaris.se/abaperls/ as
freeware.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Igor Solodovnikov (igor@.helpco.kiev) writes:
> I am using option 1 too. But there is one thing I cannot do with it. This
> thing is installing custom dll implementing extended stored procedure into
> remote computer. Do you know any solution for this problem?
See my reply for the original poster, about the toolset we use.
When our installation staff build new databases or run update scripts at
customer sites, they rarely run them directly. They package everything
in Windows Installer kits. I don't know too much about that part of the
process, but I guess this is the way to package it. Of course, learning
Wise and all that may be too much - packaing in a BAT file could work too.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 2 Apr 2004 22:15:15 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:
> Igor Solodovnikov (igor@.helpco.kiev) writes:
>> I am using option 1 too. But there is one thing I cannot do with it.
>> This
>> thing is installing custom dll implementing extended stored procedure
>> into
>> remote computer. Do you know any solution for this problem?
> See my reply for the original poster, about the toolset we use.
> When our installation staff build new databases or run update scripts at
> customer sites, they rarely run them directly. They package everything
> in Windows Installer kits. I don't know too much about that part of the
> process, but I guess this is the way to package it. Of course, learning
> Wise and all that may be too much - packaing in a BAT file could work
> too.
Thank you for answer. I must say that I also using Wise and Windows
Installer. My installer copies all required files to local computer and
then executes another utility (written by me) which can install new
instance of msde or create database through SQL script on existing
instance of SQL server/msde. When existing instance is on local computer I
can simply copy dll into sql server directory and my extended stored proc
will work. But I cant do that when existing instance is on another
computer.
Using SQL script to create database is very convenient but it has this
problem which complicates creating extended stored procs.
To solve this problem I must create one more Windows Installer package and
run it on remote computer. This is ugly.
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
