"Best Practices" way to distribute MSDE

i have decided that when distributing MSDE2000 i want to use the Microsoft
MSI file (rather than using Merge Modules).
What is the (THE, the one) best way to:
1. Install MSDE?
- checking existing named instances
- use CreateProcess and wait? Launch msiexec directly?
2. Start MSDE engine?
- SQL DMO?
- install DMO?
3. Put my database into MSDE?
- restore empty database?
- attach empty database?
- scripts?
- what about creating logins?
- what about fixing users-logins mappings?
- using TSQL?
- using DMO?
4. Backup my database once it's in production?
- TSQL?
- DMO?
5. Restore a database once it's in production?
- TSQL?
- DMO?
6. If they have to re-install MSDE, and they then restore a database, how to
re-create the logins? How do i catch that it has happened and i need to
re-map logins to users?
A quick search of Microsoft reveals about 20 different methods for
accomplishing the same things.
i want to know which one is the right one.
Personally, I've always used DMO.
On Tue, 6 Jul 2004 13:21:25 -0400, Ian Boyd wrote:

> i have decided that when distributing MSDE2000 i want to use the Microsoft
> MSI file (rather than using Merge Modules).
> What is the (THE, the one) best way to:
> 1. Install MSDE?
> - checking existing named instances
> - use CreateProcess and wait? Launch msiexec directly?
> 2. Start MSDE engine?
> - SQL DMO?
> - install DMO?
> 3. Put my database into MSDE?
> - restore empty database?
> - attach empty database?
> - scripts?
> - what about creating logins?
> - what about fixing users-logins mappings?
> - using TSQL?
> - using DMO?
>
> 4. Backup my database once it's in production?
> - TSQL?
> - DMO?
> 5. Restore a database once it's in production?
> - TSQL?
> - DMO?
> 6. If they have to re-install MSDE, and they then restore a database, how to
> re-create the logins? How do i catch that it has happened and i need to
> re-map logins to users?
>
> A quick search of Microsoft reveals about 20 different methods for
> accomplishing the same things.
> i want to know which one is the right one.
|||hi Paul, Ian,
"Paul Buxton" <psb@.NOSPAMspireite.demon.co.uk> ha scritto nel messaggio
news:knuxcy7j42cd.f6k90yoseg6f.dlg@.40tude.net...
> Personally, I've always used DMO.
>
me too, but do not attach database(s)...
instead I do execute DDL sql scripts to create objects and pre-load them...
I actually use BCP too to preload heavy populated objects...
actually, running DDL sql scripts, as long as INSERT INTO scripts can be
done via Ado/Ado.Net too...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
All depends on what data you have to put in. In my instance, a BCP of all
the data we ship does comes up to a little under a gig - for me shipping
the mdf/ldf and attaching is far more preferable
|||How do you determine if an SQL Server is already installed?
If so, how do you detect if your database is already installed?
How do you detect if you login is already created?
If you login is not already created, but your database is, how do you relink
them?
If you login is already there, but the database isn't how do you handle it?
etc etc
i think i'm beginning why Microsoft didn't write an MSDE installer that
could just install MSDE...
"Paul Buxton" <psb@.NOSPAMspireite.demon.co.uk> wrote in message
news:knuxcy7j42cd.f6k90yoseg6f.dlg@.40tude.net...[vbcol=seagreen]
> Personally, I've always used DMO.
>
> On Tue, 6 Jul 2004 13:21:25 -0400, Ian Boyd wrote:
Microsoft[vbcol=seagreen]
how to[vbcol=seagreen]
|||hi Ian,
"Ian Boyd" <admin@.SWIFTPA.NET> ha scritto nel messaggio
news:%23fiy0DIZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> How do you determine if an SQL Server is already installed?
you can (locally) check the registry, instantiate SQL-DMO (which only tells
if SQL-DMO is locally present) and try a connection to the server... this
can be done via Ado/Ado.Net too...

> If so, how do you detect if your database is already installed?
query the database catalogue for it's name... SQL-DMO/Ado/Ado.Net

> How do you detect if you login is already created?
if you can't connect with that login, it's not there... or SQL-DMO or
sp_helplogins 'login2test' via Ado/Ado.Net

> If you login is not already created, but your database is, how do you
relink
> them?
strange situation, isn't it? =;-D
eventually, just re-add your login and grant db access, as long as all
object privileges... SQL-DMO/Ado/Ado.Net

> If you login is already there, but the database isn't how do you handle
it?
you just create the database... and then go on granting db access and object
privileges... SQL-DMO/Ado/Ado.Net
just use traditional common sense =;-)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Now is there any Microsoft document that describes this typical situation of
a client's computer was re-installed, and they need their data back...
What is the best practice to do this?
Ways to put a database into a server:
Attach my empty db
Restore my empty db
Run scripts to create my empty db
Restore a users's selected backup.
Attach a users db from a previous install.
Is there a concensus on an interface, API, and routines to perform these
things are automatically as possible?
Remember, if i'm supposed to be using MSDE now instead of Jet: Jet was very
easy for the client to backup and restore. They copied the file when they
wanted a backup, and they put it into the folder when they wanted to
restore.
What is the MSDE equivalent procedures to accomplish the same task as
seamlessly for the end user without any vendor intervention or hand-holding?
What are the "Best Practices" way to distribute MSDE?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2l3qjaF8auodU1@.uni-berlin.de...
> hi Ian,
> "Ian Boyd" <admin@.SWIFTPA.NET> ha scritto nel messaggio
> news:%23fiy0DIZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> you can (locally) check the registry, instantiate SQL-DMO (which only
tells
> if SQL-DMO is locally present) and try a connection to the server... this
> can be done via Ado/Ado.Net too...
>
> query the database catalogue for it's name... SQL-DMO/Ado/Ado.Net
>
> if you can't connect with that login, it's not there... or SQL-DMO or
> sp_helplogins 'login2test' via Ado/Ado.Net
> relink
> strange situation, isn't it? =;-D
> eventually, just re-add your login and grant db access, as long as all
> object privileges... SQL-DMO/Ado/Ado.Net
> it?
> you just create the database... and then go on granting db access and
object
> privileges... SQL-DMO/Ado/Ado.Net
> just use traditional common sense =;-)
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ian,
"Ian Boyd" <admin@.SWIFTPA.NET> ha scritto nel messaggio
news:e1pXkyVZEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Now is there any Microsoft document that describes this typical situation
of
> a client's computer was re-installed, and they need their data back...
> What is the best practice to do this?
> Ways to put a database into a server:
> Attach my empty db
> Restore my empty db
> Run scripts to create my empty db
> Restore a users's selected backup.
> Attach a users db from a previous install.
as MSDE is distributed without management tools but oSql.Exe, the guidlines
I've found are in
http://support.microsoft.com/default...N-US;q325003..
in my understanding, you, as an ISV, are supposed to provide all the
functionnalities to support and manage your database(s) inside your
application(s) and/or with companion tools (thrid party or home built)
so you have to provide backup/restore functionalities, login/users
management and so on...
as regards database(s) creation, I do personally provides Transact-SQL
scripts, but attaching a shipped .mdf + .ldf solution is viable too, and is
often recommended as the simplest solution, but I do personally don't like
it, perhaps for a "purist" point of view...
anyway... for the sake of simplicity, you can use the method you better
like, as, for instance, Red-Gate new Packager tool
(http://www.red-gate.com/sql/sql_packager.htm), which provides a single file
installer for your database(s)...

> Is there a concensus on an interface, API, and routines to perform these
> things are automatically as possible?
for dayly house-keeping I do usually provide scripted job for database
backup, that rely on the SQL Server Agent, but my apps all feature (not
scheduled) backup functionalities, as long as login/user management..
database restore shoul'd usually not be a daly operation [=;-D ] , but is
provided as well... one shot operation...
again, personally I choose SQL-DMO API becouse I already provided it's
dependencies for my companion database general management tool, but
ADO/ADO.Net are good as well.. it all depends on your needs/API
skill/preferred method...

> Remember, if i'm supposed to be using MSDE now instead of Jet: Jet was
very
> easy for the client to backup and restore. They copied the file when they
> wanted a backup, and they put it into the folder when they wanted to
> restore.
>
actually you can even choose this solution, as long as your database all
have the 'Auto close' property set... this option will actually close (and
free) the physical database and transaction log files on the file system, so
that file copy operation (not SQL Server backup!) can be performed.. please
keep in mind connection pool time, which usually requires about 1 minute to
effectively close...
but, again... my personala advice is to perform dayly house-keeping actions
withour user's intervention.. that's to say: do what your users usually
skip... automatically backup their data ...
one draw back... SQLExpress, the replacement for MSDE in the SQL Server 2005
code base, will remove SQL Server Agent from the SKU, so keep in mind this
for the future.. personally I've still have to choose a scheduled
alternative, but I'll provide it for sure.
remember, you are supposed to provide all the manage/support
functionnalities

> What is the MSDE equivalent procedures to accomplish the same task as
> seamlessly for the end user without any vendor intervention or
hand-holding?
as MSDE is NOT Jet, you have a lot of extra features at a resonably price
[=;-D], but you have to take some new sagacity in your work...
nowdays, all our users do have some Access skill [potentially dangerous
=;-D ], so you shoul'd even train your users a little more, but this is
another story...

> What are the "Best Practices" way to distribute MSDE?
Microsoft provides ton of information as
http://msdn.microsoft.com/library/de...eddingmsde.asp
http://www.microsoft.com/sql/techinf...swithmsdes.asp
http://www.microsoft.com/sql/msde/te...ntegration.asp
http://msdn.microsoft.com/library/de...stsql_7b91.asp
....
personally I do provide a separate setup for MSDE, with a home built user
interface that takes care and handles all required parameter to then shell
to the setup.exe boostrap installer...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you all for you input and feedback;
and tolerating my agressive, insulting, condescending, patronizing writing
style - to everyone in every post i've ever done.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2l7qo3F9s4e1U1@.uni-berlin.de...[vbcol=seagreen]
> hi Ian,
> "Ian Boyd" <admin@.SWIFTPA.NET> ha scritto nel messaggio
> news:e1pXkyVZEHA.2216@.TK2MSFTNGP10.phx.gbl...
situation
> of
> as MSDE is distributed without management tools but oSql.Exe, the
guidlines
> I've found are in
> http://support.microsoft.com/default...N-US;q325003..
> in my understanding, you, as an ISV, are supposed to provide all the
> functionnalities to support and manage your database(s) inside your
> application(s) and/or with companion tools (thrid party or home built)
> so you have to provide backup/restore functionalities, login/users
> management and so on...
> as regards database(s) creation, I do personally provides Transact-SQL
> scripts, but attaching a shipped .mdf + .ldf solution is viable too, and
is
> often recommended as the simplest solution, but I do personally don't like
> it, perhaps for a "purist" point of view...
> anyway... for the sake of simplicity, you can use the method you better
> like, as, for instance, Red-Gate new Packager tool
> (http://www.red-gate.com/sql/sql_packager.htm), which provides a single
file[vbcol=seagreen]
> installer for your database(s)...
>
> for dayly house-keeping I do usually provide scripted job for database
> backup, that rely on the SQL Server Agent, but my apps all feature (not
> scheduled) backup functionalities, as long as login/user management..
> database restore shoul'd usually not be a daly operation [=;-D ] , but is
> provided as well... one shot operation...
> again, personally I choose SQL-DMO API becouse I already provided it's
> dependencies for my companion database general management tool, but
> ADO/ADO.Net are good as well.. it all depends on your needs/API
> skill/preferred method...
> very
they
> actually you can even choose this solution, as long as your database all
> have the 'Auto close' property set... this option will actually close (and
> free) the physical database and transaction log files on the file system,
so
> that file copy operation (not SQL Server backup!) can be performed..
please
> keep in mind connection pool time, which usually requires about 1 minute
to
> effectively close...
> but, again... my personala advice is to perform dayly house-keeping
actions
> withour user's intervention.. that's to say: do what your users usually
> skip... automatically backup their data ...
> one draw back... SQLExpress, the replacement for MSDE in the SQL Server
2005
> code base, will remove SQL Server Agent from the SKU, so keep in mind this
> for the future.. personally I've still have to choose a scheduled
> alternative, but I'll provide it for sure.
> remember, you are supposed to provide all the manage/support
> functionnalities
> hand-holding?
> as MSDE is NOT Jet, you have a lot of extra features at a resonably price
> [=;-D], but you have to take some new sagacity in your work...
> nowdays, all our users do have some Access skill [potentially dangerous
> =;-D ], so you shoul'd even train your users a little more, but this is
> another story...
>
> Microsoft provides ton of information as
>
http://msdn.microsoft.com/library/de...eddingmsde.asp
>
http://www.microsoft.com/sql/techinf...swithmsdes.asp
> http://www.microsoft.com/sql/msde/te...ntegration.asp
>
http://msdn.microsoft.com/library/de...stsql_7b91.asp
> ...
> personally I do provide a separate setup for MSDE, with a home built user
> interface that takes care and handles all required parameter to then shell
> to the setup.exe boostrap installer...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Let's just focus on how to determine if i should even install MSDE in the
first place.
1. How do i check if SQL Server 2000/MSDE2000 is already installed?
- i've read 3 about 3 different registry keys, as well as checking a
process list, and checking the services list, as well as using SQL DMO.
What if SQLDMO is not registered (or registered properly)? Do i install it
first?
i can't just look for a process, because SQL may be stopped
i can't just look at the services list, because it may be disabled. Also, i
probably can't use that instance, since it's server settings can be
different than what i require. Or it may be a trial version that will be
expiring tomorrow.
i could just always blindly install a new instance. But what if an existing
instance name conflicts with mine? What if the conflicting instance name is
actually an instance i installed last time? How do i know it's me or a
co-incidentally named instance from some other vendor? What if i'm the
client machine is out of instances, how do i handle it with a zero-user
interaction method (since the user will not have the client tools; and no
idea how to deal with it).
What if there is an existing instance, and it's mine, but noboby knows the
sa password? Do i install a fresh instance because an existing instance is
locked out?
All these questions that need to be dealt with when someone actually has to
integrate MSDE.
Compare that to Jet: put a file in my %ApplicationData% folder. And Jet is
guaranteed to be installed on any OS that Microsoft still supports.