SQL Server Express UPDATE--August 7, 2006:User Instances

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://www.appdev.com/promo/RN00430

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 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 SQLServer1MyInstance.

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

******************************************************

AVIcode SQL WP
      80 percent of all software released into production will fail due to quality issues, but proactively monitoring applications throughout the lifecycle will improve quality and reliability. Learn about the two fundamental categories of application errors and methods for quickly pinpointing the root cause of functional errors. Download the whitepaper today!
      http://www.sqlmag.com/go/whitepapers/avicode/nettroubleshooting/?code=sqlexp87

*****************************************************

Take the SQL Server Magazine Salary Survey!
      We need your help! SQL Server Magazine is launching its third SQL Server Magazine Industry Salary Survey, and we want to find out all about you and what makes you a satisfied database professional. When you complete the survey (about 10 minutes of your time), you’ll be entered in a drawing for one of five $100 American Express gift certificates. Look for the survey results--and how you stack up against your peers--in our December issue. To take the survey, go to
      https://websurveyor.net/wsb.dll/12237/SQLSalarySurvey06.htm

You Could Be a SQL Server Innovator!
      If you've developed a resourceful solution that uses SQL Server technology to solve a business problem, you qualify to enter the 2006 SQL Server Magazine Innovators Contest! Grand-prize winners will receive airfare and a conference pass to SQL Server Magazine Connections in Las Vegas, November 6-9, 2006, plus more great prizes and a feature article about the winning solutions in the January 2007 issue of SQL Server Magazine. Contest runs through September 1, 2006, so enter today!
      http://www.sqlmag.com/awards/?type=innovator

2. ==== Features =======================

Jump Start: Database Restore
      by Michael Otey

A good data-protection plan involves two core activities: backup and restore. In "Backing Up Your Database" (in the archive at http://www.sqlmag.com/Article/ArticleID/93004/sql_server_93004.html )I explain how to use T-SQL commands to back up your SQL Server 2005 Express databases. Although you can use SQL Server Management Studio Express (SSMSE) to back up your databases, T-SQL also lets you automate and schedule backups.

In this article, I cover basic full-backup restore. Before jumping into the T-SQL RESTORE commands, I should point out that similar to SQL Server 2005, SQL Server Express supports multiple types of backup and restore options including full, differential, and log backups.

As with the SQL Server Express database backup options, you can restore a database by using either SSMSE or T-SQL commands from SQLCMD or Query Editor. In "Backing Up Your Database," I used T-SQL commands to show you how to back up a database, so I'll show you the T-SQL RESTORE commands in this article. The following code restores the MediaCollection database from a previous full-database-backup disk file named MediaBackup.bak:

RESTORE DATABASE MediaCollection
FROM DISK =
'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBACKUP MediaBackup.bak'

As you can see, the RESTORE command for a full backup is straightforward. You provide the name of the database you want to restore after the RESTORE DATABASE command, the name of the disk backup file, followed by the FROM DISK = clause. It's that simple.

***** HOT SPOT: Neverfail ************************

Neverfail WP
      Want the convenience of a server cluster without the expense? Learn about server cluster alternatives that provide high availability, preventative maintenance, and fallover capabilities at pricing that fits your budget.
      http://www.sqlmag.com/go//whitepaper/neverfail/sqlclustering/?code=sqlexp87

******************************************************

Check It Out: Connection String Resource
      by Michael Otey

When you're writing client applications for SQL Server Express (or any other database for that matter), you always need to use the proper syntax for the connection string that your application uses to connect to a database. One handy resource site that provides connection strings for many database products is http://www.connectionstrings.com . If you aren’t familiar with this Web site, you should check it out.

3. ==== Resources and Events ===========

SQL Server Magazine Connections Conference
      Now in its sixth year, SQL Server Magazine Connections returns November 6-9, at Mandalay Bay Resort in Las Vegas. Get down to business today with SQL Server 2005. Interact with and learn from an all-star lineup. Register for one event and attend sessions of the concurrently run events for FREE!
      http://www.devconnections.com

Cross-Platform Data Roadshow
      Oracle professionals will cover key concepts about Oracle and SQL Server in enterprise database computing. This event provides invaluable information about the benefits of 64-bit computing on the Windows platform, SQL Server BI for Oracle, high-availability proof points for SQL Server and Oracle, and much more.
      http://www.windowsitpro.com/roadshow/sqloracle/?code=SQL0807

Consolidating SQL Server Deployments
      Does your SQL Server deployment suffer from low utilization rates, suboptimal availability, and management challenges? Learn to overcome these difficulties by deploying a database utility that can help lower your cost of ownership by 70 percent and can react to changes within 30 seconds. Live Web Seminar: Tuesday, August 22
      http://www.sqlmag.com/go/seminars/polyserve/consolidatesql/?partnerref=0809emailannc

Five Keys to Choosing the Right Antispyware Solution
      Randy Franklin Smith outlines five evaluation points to consider when choosing your antispyware solution in this free podcast. Download it today!
      http://www.windowsitpro.com/go/podcasts/pctools/antispyware/?code=0809emailannc

4. ==== Featured White Paper ============

How Effective is Your Antivirus?
      Antivirus or patching software alone isn’t enough to protect your valuable systems from spyware. Learn how an enterprise antispyware solution gives you an affordable--and most important, effective, solution to spyware. Download the free whitepaper today!
      http://www.windowsitpro.com/go/whitepapers/sunbelt/antispyware/?code=0809featwp

===================================

Wanted: your reviews of products you've tested and used in production. Share your experiences and ratings of products to "[email protected]" and get a Best Buy gift certificate.

5. === New SQL Server Express Product ===

Easily Encrypt Database Objects
      by Blake Eno

Ecatenate announced dbLockdown 2.0, a solution that helps you secure your SQL Server 2005 and SQL Server 2005 Express intellectual property. dbLockdown's interface lets you see all database objects across your enterprise and Internet servers. You can encrypt stored procedures, views, user-defined functions (UDFs), database triggers, and server triggers in one or more servers and databases. Each database object that's encrypted is automatically archived for easy retrieval. dbLockdown also lets you edit the T-SQL script for database objects. For more information, contact Ecatenate at [email protected].
      http://www.ecatenate.com

6. ==== Exclusive Email Offers ====

Monthly Online Pass--Only $5.95 per month!
      Includes instant online access to every article ever written in SQL Server Magazine, plus the latest digital issue. Order now:
      https://store.pentontech.com/index.cfm?s=9&promocode=eu2168um

Save $40 On Windows IT Pro Magazine
      Subscribe to Windows IT Pro magazine today and SAVE up to $40! Along with your 12 issues, you'll get FREE access to the entire Windows IT Pro online article archive, which houses more than 9,000 helpful IT articles. This is a limited-time offer, so order now:
      https://store.pentontech.com/index.cfm?s=1&promocode=eu2068uw

==== Contact Us ====

About the [email protected]
About the [email protected]
About technical questions-- http://sqlforums.windowsitpro.com/web/forum/default.aspx?forumid=10
About product [email protected]
About your [email protected]
About sponsoring an issue of SQL Server Express UPDATE--Richard Resnick, [email protected]

SQL Server Express UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
      https://store.pentontech.com/index.cfm?s=9&cid=51&promotionid=1491

Manage Your Account
You are subscribed as %%$email%%. To unsubscribe from this email newsletter, click here
      http://lists.sqlmag.com/u?id=%%SUBSCRIBER_ID_TAG%%

To manage your email account, simply log on to our Email Preference Center.
      http://www.sqlmag.com/email

View the SQL Server Magazine Privacy Policy.
      http://www.sqlmag.com/aboutus/index.cfm?action=privacy

SQL Server Magazine is a division of Penton Media, Inc.
221 East 29th Street
Loveland, CO 80538
Attention: Customer Service Department

Copyright 2006, Penton Media, Inc. All Rights Reserved.