In this issue, you'll learn how to run multiple SQL Server
Express User Instances and use the T-SQL RESTORE command. I also
mention a Web site that provides connection strings for many
database products.
Subscribe to SQL Server Magazine and make sure you add
[EMAIL PROTECTED] to your list of allowed senders and
contacts:
http://lists.sqlmag.com/t?ctl=3413C:7F5CF8
PLEASE VISIT OUR SPONSORS, WHO BRING YOU THIS UPDATE FOR FREE:
FREE SQL Server 2005 Training CD-ROM!
http://lists.sqlmag.com/t?ctl=34143:7F5CF8
AVIcode SQL WP
http://lists.sqlmag.com/t?ctl=34138:7F5CF8
Neverfail WP
http://lists.sqlmag.com/t?ctl=34139:7F5CF8
=== Contents ==
August 7, 2006
Commentary
- An Instance of Understanding
Features
- Jump Start: Database Restore
- Check It Out: Connection String Resource
Resources and Events
- SQL Server Magazine Connections Conference
- Cross-Platform Data Roadshow
- Consolidating SQL Server Deployments
- Five Keys to Choosing the Right Antispyware Solution
Featured White Paper
- How Effective is Your Antivirus?
New SQL Server Express Product
- Easily Encrypt Database Objects
Exclusive Email Offers
- Monthly Online Pass--Only $5.95 per month!
- Save $40 On Windows IT Pro Magazine
== Sponsor: AppDev
FREE SQL Server 2005 Training CD-ROM!
Start learning SQL Server 2005 today with cutting edge
training from AppDev. Get a FREE SQL 2005 training CD (a $115
value!) taken directly from our new course. Click the link below
for your FREE SQL Server 2005 training CD.
http://lists.sqlmag.com/t?ctl=34143:7F5CF8
1. Commentary
An Instance of Understanding
by Michael Otey
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 DefaultServerName\InstanceName
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=MyDataDirectory\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
