SQL Server User Instance Question
-
I was wondering whether anybody could confirm an assumption for me. The information on SQL Server Express talks a lot about its special "User Instance" mode and how it can help to simplify deployment because you can go back to just copying in a file to set up your database, and how this is great for dealing with limited user security environments. However, I have some questions surrounding whether or not this is really as great as it's cracked up to be -- I saw somewhere, for example, that made it sound like you have to be coding your application in .NET in order to use it; and it sounded like it pretty much gives the end user full access to their personal SQL Server instance, which I might not necessarily want from an application perspective. The assumption that I wanted to confirm was this… Let's say that you're NOT using the "User Instance" mode. What do you then need to do in order to create the initial shell of your application database at deployment time? I was assuming that you end up having to have some sort of SQL script that does the database creation instead (including, I assume, assigning proper rights to tables to BUILTIN\Users as necessary). Is that correct?
-
That's what I would do. In my opinion, this user instance mode feature can make more problems - say - someone has a Standard or higher edition of SQL Server installed - user instance mode is useless then.
In my last project I built database from scripts in installer. This gives you the best control over the application setup.
...and your only reply is slàinte mhath
-
What is installer?
-Kyle
-
ah
In short: you have project category in Visual Studio: Other Project Types/Setup project. You have to create one and add a custom action (basically a .NET assembly) that will run your database script.
this is a longer discussion about setups, custom actions and other stuff
...and your only reply is slàinte mhath
-
Why does the user instance mode cause problems? How does it become useless if someone has Standard or higher installed?
Thanks,
Kyle
-
Higher versions do not support User Instance Mode. So, if you rely on it, you may get burned. You cannot connect to user instance using tcp/ip, only named pipes. Connection string has to be altered for user to enable him to connect to his instance - this may, or may not be an issue, depending how DBA sees his range of duties. I suppose that user instances consume additional memory and disk not necessarily.
There are also other considerations (after BOL):
- One user can only have one user instance.
- Replication will be disabled.
- User Instance does not support SQL Server Authentication. Only Windows Authentication is supported.
- The network protocol support for user instances is local named pipes only.
- The user instance shares the registry entries of the parent instance.
- There is no support for user instance with native code. This feature is only supported with ADO .NET.
- Full text Search is not supported on a SQL Server Express user instance.
- WMI Provider for Server Events will not be supported on the dynamically spawned user instances. This should still work on the parent SQL Server Express instance. For more information on WMI provider, see WMI Provider for Server Events in SQL Server 2005 Books Online.
...and your only reply is slàinte mhath
-
Thank you very much.
-Kyle
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply