Recommended Approach for deploying SQL Server Express with shrink wrap software

Microsoft SQL Server

I'm an ISV, and currently the main "shrink wrap" application that I develop
uses MS JET 4.0 as the backend database. My application's "document" is
just an mdb file (with a file extension that is associated with my app).
Deployment is quite simple, and from my users' perspective it's completely
transparent. Most have no idea that I'm using a database engine or what
database engine I'm using under the covers. For the most part, life is
good, and I hesitate to leave this simplicity.

But for a few reasons I'm strongly considering switching my main "shrink
wrap" application to use SQL Server 2005. I really like many of the new
features, especially CLR in the database, and merge replication over https.
I've developed SQL Server apps for enterprise type of deployments, where one
can assume that there will be IT staff to take care of getting SQL Server up
and running, etc. But for my "shrink wrap" application used by the masses,
I can make no such assumption. Most of my users probably haven't even heard
about SQL Server, nor should they have to.

So here's my dilemma. During the *default* installation of my application
where the average user just presses "Next", "Next", "Next", "Install",
"Done" (advanced settings would allow complete control), do I just install
SQL Server Express, creating my own instance, if my own named instance
doesn't already exist? What about the 16 instance limit? If everybody did
this, we'd hit the 16 instance limit pretty quickly. And wouldn't there be
performance issues with each application using their own named instance? Or
should I just use an existing instance, if one exists?

What is the recommended approach for installing and using SQL Server Express
as a true JET replacement. (The answer can not require that the average
user be forced to make any decision requiring knowledge of SQL
Server/instances.)

Troy
hi Troy
Micorosoft, since the release fo MSDE Rel A, the very first free edition for
everyone, no longer recommends to set up a private instance for each ISV
untill such a circumstance is strictly required (say particular
collation/sort order requirements, or privacy matters or the like...)
SQLExpress stresses this rule suggesting a "common instance" named
\SQLExpress, and, if you're not compelled for other reasons, you should
install such a common instance or take advantege of it, if already
present...

if you run out of instances, you won't be able to install a new one...
that's all...

of course there are implications running several SQL Server instances, as
each of them will consume resources... each of them will be loaded into
memory, but this is not to say that each instance adds the very same
footprint, but they can quickly contend for system resources on heavy loaded
systems...

actually this is the path you should follow, if no compelling reasons for a
private instance exist...

the SQLExpress bootstrap installer includes an UI to perform personal
settings, or you can provide your own setup gui and provide all required
parameters to the setup.exe bootstrap installer, say, for instance,
"setup.exe /qb INSTANCENAME=SQLExpress [email protected] SECURITYMODE=SQL
DISABLENETWORKPROTOCOLS=2 ADDLOCAL=SQL_Engine"
http://msdn2.microsoft.com/en-us/library/ms143793.aspx
http://msdn2.microsoft.com/en-us/library(d=robot)/ms144259.aspx
so you can previously collect all required/desired information you need and
pass them on...
under MSDE time frame I personally provided a "companion" boostrap installer
of MSDE to collect all required info and shelling then to the actual
setup.exe providing all gathered parameters as commandlind params...

again, consider that, usually, even on a little lan, you will not install
the engine on each machine basis as you do for your application(s), but you
install the engine only on the machine that will act as "the server".. you
are used to SQL Server already, so keep this in mind...

another story is when and if you decide to use a SQLExpress available only
feature known as User Instance (RANU),
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp
..
this can be convenient in some scenarios for ease of use and maintenance,
but, of course, should not be used in multi-machine/multi-user ones...

personally I'll continue providing a separate setup path, 1 setup for
application(s) that must be executed on each machine and 1 setup (actually
the SQLExpress setup) for the engine itself, to be executed on the machine
acting as "the server"...
this setup can include (or not, depending on your design you can put that in
an additional companion tool of your app) the physical database(s)
installation procedure, task I do perform with something similar to
http://msdn.microsoft.com/msdnmag/issues/04/09/customdatabaseinstaller/ I
already produced since 1999 ( :-D )
in my case, it's a separate companion tool that is used to perform both
initial installation of the database(s) and successive (eventual)
database(s) upgrade... I do strongly like this path as it support both tasks
and even include ability to maintain db design under source code control for
histroy and management...
all this, obviously, IMVHO....
This sounds like a reasonable way to go. Might someone be able to point me
to where this is offiically documented by MS?



Like you, I've written a simple program to shell command-line arguments to
help our IT staff to install MSDE, and I'm very comforable and familiar with
this aspect of the installation. But in a different context, where this
installation is performed by the masses, it must be transparent. It would
be pointless to ask users what they'd like for the "sa" password or what the
"securitymode" should be! So what are the recommended way to install the
common "SQLServer" instance (ie. "sa" password, security mode, disable
network protocols, etc.)?


Of course, and my installation program would provide an "Advanced Settings"
area where the setup could be completely customized for the more unique
cases like this.


I read that article, and it actually raises the point that normal users
running as non-Administrators can't attach database files ad hoc to their
local server, unless using User Instances. A MAJOR reason for using SQL
Server Express is to allow merge replication subscriptions over the
internet/https. But the article mentions that not being able to use
replication is a limitation of User Instances. So I either have to require
users run as Administrators (bad idea), give up on using replication
features (and thus the whole point of switching to SQL Server 2005), or I
need a way for my program (but not users) to know the "sa" password (sounds
like an impossible security issue). Perhaps this is a reason for me to
install my own instance (or to stay with JET!).


Sounds great in a non-shrink-wrap deployment environment. I'd really be
stretching many of my users to tell them "run this setup on the machine
which acts as the server", and "run this on all the client machines". I can
appreciate how much this helps IT staff to get things going, but it's still
to technical for my users.


I've also been using a strategy just like this. I keep a database structure
version that I can read out of any of my databases, and I have scripts than
can "upgrade" the database if need be. The scripts are part of my
application, and are kept in Source Safe.

By the way, this is getting more and more complicated, so does staying with
JET and starting to use JET Replication seem like a bad or good idea?
Choosing it back in 1999 was a very successful move for me, and has given my
development/deployment environment alot of simplicity. I really love how
the entire database is kept in one file and deployment is practically
trivial. I'm starting to wonder if the benefits of porting my app to SQL
Server and using all its new shiny features are enough to outweigh the added
complexity of trying to use SQL Server to emulate a file-based application
(in a way that is perfectly natural for Jet).
hi Troy,
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp
Engine Specifications
.....
SQL Server Express by default installs as a named instance called
SQLEXPRESS. This particular instance may be shared among multiple
applications and application vendors. We recommend that you use this
instance unless your application has special configuration needs.

actually, AFAIK, no guide is provided at all...
sa password... you should set it to something "difficult" to hack.. but what
then? should you provide feedback of that to end users? should you store it
somewhere for user's review? I understand your problem, but, as you already
know, SQL Server is NOT Jet, and some level of extra complexity is "by
default" :D
security mode... depending on your needs... I know that my apps are often
used in workgroups not under a domain controller so I obviously require
mexed security mode... all my apps include an internal module to add/manage
logins (mapping them to db-users and db-roles I set up for security access
[and my own external management tool is provided for eventual skilled
administrators)... as long as scripted jobs to define a minimal backup
strategy.. I have now to modify it as SQL Server Agent is no longer provided
with SQLExpress in order to take advantage of the native Windows scheduler:(
network protocols... you know the default.. but, again, if the db engine
should be available on the lan...

the question is... should you trust your customers/users?
I do trust them for this kind of things, it's their system, their money (ok,
my time).. I do not trust them for dayly backups or the like... this is
becouse I miss the SQL Server Agent :D


this is a "standard" SQL Server policy... RANU provides a sort of
"workaround" of that, but other implications raises..


actually things are quite different..
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
SQLExpress provides the Service Broker but only as a subscriber...
it supports Merge and Transactional replication but, again, only as a
subscriber...
HTTP EndPoints for WebServices are not available as well...

you should actually only run the account running SQL Server as a local admin
or LocalSystem or whatever.. actually even to much permissions are given
under these roles...

perhaps you can define an "internal" used only login you create at database
installation time to perform attach and the like..
you can this way rely on your "internal" defined solution for task requiring
administrative permissions... the only requirement is running the initial
setup as local admin in order to grant the required privileges to create
your required database(s) the way you like it/them to be created, create
your "internal used" login with sysadmin membership and you are done...
every time your app requires to do "critical" tasks you only need to open a
new connection under this "admin login" credentials...

I understand, but a minimal skill should be provided/required :D
what if they all installs "local" instances? they all run separated
applications?
I do not think so.. I trust you already provided a way to tell "this is a
client, do not install the Jet database here as this machine will connect to
the other one"..

never used Jet replication... I do not know how stable it is, even in a
multi-user scenario...

things always have to get complicated, or we loose our job opportunities :D
behind the joke, it'sobvious that Jet solutions are "simple and clear"... no
extra needs but MDAC (and not always) and a db filecopy... and for single
users this makes sense... no bloat...
but for even very very small workgroups of 2 machines I do not trust it any
more... I've seen to much mdb databases trashed becouse of power outage or
bad network writes, or simply becouse of exceptional user activity where the
db must be compacted/repaired at least once an hour.. IMHO, SQLExpress
solves this trust/stability requirements.. ok, some extra skill requirements
are needed, but usually you run SQL Server with no harm becouse of its
implicit robustness, autotuning, security features...
ah.. you obviously already know it, but just in case (:-D) ... SQL Server
does not have to emulate a file-based application as it is a client-server
solution... reduced network trafic in order to minimize performaces both on
the server and on the clients... no SELECT * FROM millions_rows_table :D:D

again, all this, IMVHO
Excellent link. Thanks, Andrea!



I can accept that. As long as the brunt of the complexity falls on my
shoulders, not my users! And as long as the weight of complexity on my
shoulders doesn't cripple my productivity.



In my current Jet application, I perform an auto-backup whenever some exits
the application. This wouldn't scale well to many users, obviously, so I'll
have to think about tweaking my backup strategy to accomodate this, too.



I'd leave that up to an advanced user. "Secure by default" would mandate
that I don't provide install SQL Server Express with external TCP/IP access
by default.



Define "trust"! :^) Do we as developers *understand* our users. I'm all
for providing "advanced" settings to allow power users to do almost anything
they want. But we need to provide a path that "just works" for
less-than-power-users!



I used to trust my users to backup their database files. But after too many
support incidents where all of their data was corrupted due to a hardware
failure, I eventually added auto-backup. I sleep better at night!



I (embarassingly) just hadn't connected the dots in seeing how this might be
a problem. Thinking about using SQL Server in a non-corporate (now "shrink
wrap") environment is a slight paradigm shift.



I'm quite pleased to see that Service Broker, Reporting Services and
Full-text Search are either already or will be supported in SQL Server
Express!



That's a great idea, Andrea. I do require an administrative privileges to
install my software. So I could take advantage of this administrative
privilege during "installation time" and install this internal
administrative login into SQL Server Express. From a security perspective I
see one slight flaw, however. How does one keep the password for this login
a secret, yet know-able to your application? If I just hard-code the
password, than it seems like I'm relying on security by obscurity, and that
someone might eventually crack it or see it in my source code.



This might be wishful thinking out in the non-corporate, shrink-wrap world
of deploying software to the masses. :^)



Actually I never install a database file. It's entirely created from
scratch by my application when the user selectes File|New.



Seems like it!



Aren't SQL Server files just as prone to failure. I'm assuming SQL Server
has a much better reputation than jet, because it's typically run in a
controlled server room somewhere safe. But now that it's running on average
people's machines out in the wild, isn't it just as vulnerable to file
corruptions due to hardware failure as Jet is? Or were the corruptions due
to programming/design deficiencies in the jet driver itself?



Sure. Like I said, I've written entire applications for SQL Server before.
Just not for mass deployment. Of course I would have to change alot of this
"SELECT * FROM millions_rows_table" in my Jet-based application. But the
reward would be a much more scalable application that can be used in
personal scenarios, as well as big-enterprise scenarios.

Thanks so much for your very helpful advice, Andrea. I think I'm starting
to wrap my head around this better.
hi Troy,
yep... SQL Server Agent based (for MSDE) or AT/SCHTASKS based scheduled
backups can help a lot, in this vision..

ok... it's a correct approach


1st... will they pay me? :D

again, correct... and again, for this reasons, I do provide "manual" backup
the user can perform as long as automated defaults...


AFAIK, FullText is not available to SQLExpress
and as Service Broker+Replication are available as subcriber only solutions,
I do not think you can "plan" on it if you think to very very small business
scenariow where only SQLExpress instances are involved..


if your code get cracked, you (unfortunately) have other troubles then the
"security" of the "internal" login.. but I see this "possibility" as a good
candidate for scenarios where you need to perform things the current logged
in user is not allowed to...
in a Jet to SQL scenario, often developers try to "minimize" the impact of
saying "you are not allowed to (attach a db, backup the db, restore the db,
....)" becouse of a neutral set of rules are "coded" in the business
layer... this is often solved granting all sysadmin membership.. I do not
like this path, I do prefer to grant my "standard dayly" logins/users as
much permissions as they really need, so no one is sysadmin or the like (as
it should be... you do not log in as admininistrator to retrieve/modify
data)... I do prefer to "risk" the other way... ie: create a "hidden
ineternal/only" administrative login to perform tasks the current logged in
user is not allowed to...

ok.. this depends on a particular "feature" of your design...

actually not... if a system failure occurs, the machine crashes, a power
outage occurs, at SQL Server startup a "recovery" phase is performed and all
pending transactions are rolled back/rolled forward... a db can be trashed
but I really have seen few of them in over 6 years... SQL Server is very
robust indeed...