|
|
You're in the
sql server msde
group:Recommended Approach for deploying SQL Server Express with shrink wrap software
sql server msde:
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.) Thanks, Troy
[quoted text, click to view] > MS ... 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... This sounds like a reasonable way to go. Might someone be able to point me to where this is offiically documented by MS? [quoted text, click to view] >> What is the recommended approach for installing ... > 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 SAPWD=@123456789o SECURITYMODE=SQL > ... 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.)? [quoted text, click to view] > 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 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. [quoted text, click to view] > 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... 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!). [quoted text, click to view] > 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"... 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. [quoted text, click to view] > ... > 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... 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). --Troy
hi Troy
[quoted text, click to view] Troy Wolbrink wrote: > ... > 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? 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... [quoted text, click to view] >What about if you run out of instances, you won't be able to install a new one... > the 16 instance limit? If everybody did this, we'd hit the 16 > instance limit pretty quickly. that's all... [quoted text, click to view] >And wouldn't there be performance of course there are implications running several SQL Server instances, as > issues with each application using their own named instance? 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... [quoted text, click to view] >Or should I just use an existing instance, if one exists? actually this is the path you should follow, if no compelling reasons for a private instance exist... [quoted text, click to view] > What is the recommended approach for installing and using SQL Server the SQLExpress bootstrap installer includes an UI to perform personal > 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.) 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 SAPWD=@123456789o 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.... -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0 (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface) --------- remove DMO to reply
hi Troy,
[quoted text, click to view] Troy Wolbrink wrote: > > This sounds like a reasonable way to go. Might someone be able to > point me to where this is offiically documented by MS? 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. [quoted text, click to view] > actually, AFAIK, no guide is provided at all...> 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.)? 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 [quoted text, click to view] > > 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. this is a "standard" SQL Server policy... RANU provides a sort of "workaround" of that, but other implications raises.. [quoted text, click to view] >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. 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... [quoted text, click to view] > So I either have to require users run as Administrators (bad idea), 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... [quoted text, click to view] > give up on using replication features (and thus the whole point of perhaps you can define an "internal" used only login you create at database > 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!). 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... [quoted text, click to view] > Sounds great in a non-shrink-wrap deployment environment. I'd really I understand, but a minimal skill should be provided/required :D> 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. 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".. [quoted text, click to view] > By the way, this is getting more and more complicated, so does never used Jet replication... I do not know how stable it is, even in a > staying with JET and starting to use JET Replication seem like a bad > or good idea? multi-user scenario... [quoted text, click to view] >Choosing it back in 1999 was a very successful move for things always have to get complicated, or we loose our job opportunities :D> 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). 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
[quoted text, click to view] > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp Excellent link. Thanks, Andrea! [quoted text, click to view] > ... as you already know, SQL Server is NOT Jet, and some level of extra > complexity is "by default" :D 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. [quoted text, click to view] > ... 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:( 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. [quoted text, click to view] > network protocols... you know the default.. but, again, if the db engine > should be available on the lan... 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. [quoted text, click to view] > the question is... should you trust your customers/users? 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! [quoted text, click to view] > 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 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! [quoted text, click to view] >> 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. > > this is a "standard" SQL Server policy... RANU provides a sort of > "workaround" of that, but other implications raises.. 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. [quoted text, click to view] > 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... 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! [quoted text, click to view] > perhaps you can define an "internal" used only login you create at > database installation time to perform attach and the like.... 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. [quoted text, click to view] > I understand, but a minimal skill should be provided/required :D This might be wishful thinking out in the non-corporate, shrink-wrap world of deploying software to the masses. :^) [quoted text, click to view] > 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".. Actually I never install a database file. It's entirely created from scratch by my application when the user selectes File|New. [quoted text, click to view] > things always have to get complicated, or we loose our job opportunities > :D Seems like it! [quoted text, click to view] > I've seen to much mdb databases trashed becouse of power outage or bad > network writes, 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? [quoted text, click to view] > 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 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. --Troy
hi Troy,
[quoted text, click to view] Troy Wolbrink wrote: > > 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. yep... SQL Server Agent based (for MSDE) or AT/SCHTASKS based scheduled backups can help a lot, in this vision.. [quoted text, click to view] > I'd leave that up to an advanced user. "Secure by default" would ok... it's a correct approach> mandate that I don't provide install SQL Server Express with external > TCP/IP access by default. [quoted text, click to view] >> the question is... should you trust your customers/users? 1st... will they pay me? :D> > Define "trust"! :^) [quoted text, click to view] >Do we as developers *understand* our users. I'm all for providing again, correct... and again, for this reasons, I do provide "manual" backup >"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! the user can perform as long as automated defaults... [quoted text, click to view] > I'm quite pleased to see that Service Broker, Reporting Services and AFAIK, FullText is not available to SQLExpress> Full-text Search are either already or will be supported in SQL Server > Express! 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.. [quoted text, click to view] > That's a great idea, Andrea. I do require an administrative if your code get cracked, you (unfortunately) have other troubles then the > 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. "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... [quoted text, click to view] > Actually I never install a database file. It's entirely created from ok.. this depends on a particular "feature" of your design...> scratch by my application when the user selectes File|New. [quoted text, click to view] > Aren't SQL Server files just as prone to failure. I'm assuming SQL actually not... if a system failure occurs, the machine crashes, a power > 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? 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... -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0 (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface) --------- remove DMO to reply
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |