Dec 25, 2007

SQL Server (user) instances and AutoAttach DB

Developers often need to attach databases on demand and then start using them immediately. However before developers can start working with DB's they need to first attach the DB, then create a SQL login and then add that login to the DB users list. with SQL Express this is no longer a requirement.

With SQL Express we can use Auto Attach DB's and User instances.
SQL 2005 Express and Full editions both support autoAttachment of DB's, however User instancing is only supported with SQL Express.

To enable AutoAttachment of DB's use : AttachDbFilename=db.mdf in connection string.
When you use "AttachDbFilename=|DataDirectory|db.mdf" the connection string automatically looks for the DB in the AppData folder of the Web Application.

However there is a limitation that if the user is not a administrator, he would still not be able to AutoAttach the DB. we can solve this by using "User instances".
User instance is another SQL server instance and runs under the credentials of the currently logged in user, thus no need to give the user administrative privileges.
==================================================
User Instances is a feature that makes SQL Server 2005 Express different from other SQL Server editions. Before I explain User Instances, you need to understand that a SQL Server instance is essentially an in-memory occurrence of the sqlservr.exe executable program. Different SQL Server editions support different numbers of instances. For example, the SQL Server 2005 Enterprise Edition supports 50 instances, and the SQL Server 2005 Standard, Workgroup, and Express editions each support 16 instances. Each instance runs separately and has its own set of databases that aren't shared by any other instance. Client applications connect to each instance by using the instance name.

Typically, the first SQL Server instance you install becomes the "default" instance. The default instance uses the name of the computer on which it's installed. You can assign a name to subsequent instance installations, so they're called "named" instances. During the installation process, you can assign any name to a named instance. Client applications that want to connect to an instance use the \ convention. For example, if the default instance name is SQLServer1 and the instance name is MyInstance, the client application would connect to the named instance by using the server name SQLServer1\MyInstance.

As with the other SQL Server editions, SQL Server Express supports the default instance and named instances, but SQL Server Express uses SQLExpress as the default instance name rather than the name of the computer system.

In addition to regular SQL Server instances, SQL Server Express also supports User Instances. User instances are similar to named instances, but SQL Server Express creates user instances dynamically, and these instances have different limitations. When you install SQL Server Express, you have the option of enabling User Instances. By default, User Instances aren't enabled. After installation, you can enter the sp_configure command in SQL Server Management Studio Express (SSMSE) or the sqlcmd tool by using the following syntax:

sp_configure 'user instances enabled','1'

To disable User Instance support, replace 1 with a 0 in the sp_configure command.

User Instances were designed to make deploying databases along with applications easier. User Instances let users create a database instance on demand even if they don't have administrative rights. To utilize User Instances, the application's connection string needs to use the attachdbfilename and user instance keywords as follows:

Data Source=.\SQLExpress;integrated security=true;
attachdbfilename=\MyDatabase.mdf;user instance=true;"

When an application opens a connection to a SQL Server Express database in which User Instances are enabled and the application uses the attachdbfilename and user instance keywords, SQL Server Express copies the master and msdb databases to the user's directory. SQL Server Express starts a new instance of the sqlserver.exe program and SQL Server Express attaches the database named in the attachdbfilename keyword to the new instance.

Unlike common SQL Server instances, SQL Server Express User Instances have some limitations. User Instances don't allow network connections, only local connections. As you might expect with the network-access restriction, User Instances don't support replication or distributed queries to remote databases. In addition, Windows integrated authentication is required. For more information about SQL Server Express and User Instances you can read the Microsoft article "SQL Server 2005 Express Edition User Instances" at
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp

kick it on DotNetKicks.com

10 comments:

Anonymous said...

FYI - according to the MSDN page
http://msdn.microsoft.com/en-us/library/bb264564.aspx:

"If the user doesn't already have SQL Server Express, you can either include SQL Server Express with your application or your users can download it from the Web. No additional configuration is required to support user instances."

So you don't have to enable user instances in the database instance.

Anonymous said...

Great post as always ,thanks for sharing information . Company Logo Design

Knox Karter said...

"Hi,
I like your blog very much! It is found me so interesting and informative... Thanks very much for sharing this amazing information over here…"


Social Media Optimization Service

logo bench said...

I'm still learning from you, but I'm trying to achieve my goals. I certainly enjoy reading all that is posted on your blog.Keep the information coming. I loved it!
create a custom logo

Knox Karter said...

Thanks for your post, I like this post very much.

Ready-Made Art Logo

justin albert said...

Thank you for sharing this! Just what I’ve been searching for. Great info!


Customize Facebook Fan Page

Knox Karter said...

Hey Great Comments on this blog. And the Information is also Great. Thanks, for a really nice read.

Logo Design

Knox Karter said...

This is a really good read for me, I must say you put your best effort for this blog.

Online stores

Knox Karter said...
This comment has been removed by the author.
Knox Karter said...

"Hi,
I like your blog very much! It is found me so interesting and informative... Thanks very much for sharing this amazing information over here…"

Relationship Problems