It's time for another release post, this time for RC0 of SQL Server 2008 Express and SQL Server 2008 Express with Advanced Services. Here is the information that everyone is waiting to hear about the SQL Express product line. (Note: If you're the impatient type you can skip to the end of this post to find the instructions for installing SQL Express.)

Physical Limitations

SQL Express 2008 has the same physical limitations that existed in SQL Express 2005:

• 1 CPU
• 1 GB Memory
• 4 GB of user data per database

Since people always ask - you can run SQL Express on machines that exceed these limits, SQL Express just ignores any addition CPUs or memory. SQL Express will take advantage of multiple cores on the single CPU by starting a scheduler for each core.

Feature Support

The same features available in SQL Express 2005 are still available in SQL Express 2008, if you need a refresher on those you can find them in the Feature Comparison chart in Books Online. This post only calls out the features in SQL Express that are new in 2008.

* The SqlPS command line tool can be enabled in SQL Express by installing Windows PowerShell 1.0 before installing SQL Express.

** Policies can be created in SQL Express and run manually. There is no support for automated policy based management.

*** Synchronization Services support in SQL Express requires that you install the component separately. It is available from the SQL Server 2008 Feature Pack on the Microsoft Download Center.

Architecture Support

SQL Express and SQL Express Advanced will be release in both 32-bit and 64-bit (x64 0nly). As in the past, SQL Express will available for download from the Microsoft Download Center and there will be multiple packages depending on your specific needs. The following types of packages will be released at RTM. (Not all of these will be available at RC0)

SQL Express

32-bit only installation package (x86 platforms only)

32-bit WoW installation package (x86 and x64 platforms)

64-bit native installation packages (x64 platforms only)

SQL Express Advanced

32-bit only installation package (x86 platforms only)

64-bit native installation packages (x64 platforms only)

Installing SQL Express products

SQL Server 2008 introduces some new prerequisites to installation and as in the past, these prerequisites are not included in the SQL Express packages. You must install these prerequisites manually before installing either SQL Express product or the installation will fail.

SQL Express prerequisites

.NET Framework 2.0 SP2

Windows Installer 4.5

SQL Express Advanced prerequisites

.NET Framework 3.5 SP1

Windows Installer 4.5

Windows PowerShell 1.0

Note 1: While the .NET Framework 2.0 SP2 is the technical prerequisite for the core SQL Express package, I've only found this available as part of the .NET Framework 3.5 SP1 (Beta at the time of RC0), which includes all services packs of the 2.0 and 3.0 release of the framework as well.

Note 2: You will need to reboot your computer after installing Windows Installer 4.5 on most platforms. SQL Server 2008 will fail to install if the reboot after Windows Installer 4.5 has not been completed.

Once all the pre-requisites have been installed you can install SQL Express or SQL Express Advanced by downloading the desired package and double clicking it to start the installation.

Download the .NET Framework 3.5 SP1 Beta from here.

Download Windows Installer 4.5 from here. (Pick the appropriate package for your environment.)

Download Windows PowerShell 1.0 from here. (Pick the appropriate package for your environment.)

Download SQL Server 2008 Express from here. (SQL Express links are at the bottom of the Instructions section.)

Getting Help

You can post Express specific question to the SQL Server Katmai Express forum.

You can post SQL Server 2008 general questions on the SQL Server Katmai forums.

You can file bugs and feature requests on SQL Server Connect site.

Have fun!

-Mike

I asked for your oppinions about a new Report Designer in one of my earlier posts in this blog. I wanted to thank everyone for taking the time to offer feedback and for those of you who even filed a bug or two. As you've probably already guessed from the title, our ultimate decision was to stick with Business Intellegence Developers Studio for report design in SQL Express with Advanced Services 2008.

 It's always a tough decision to make trade-offs between different scenarios and this case was no different. Stick with the rich, developer experience offered by the Visual Studio integration of BIDS or move to a more end-user focused experience that is friendly looking but doesn't have the developer features. (I know you're going to suggest that we just hand out both, but hey, it's a free product so you only get one. We have to share the new stuff with the other Editions too.) We desided to continue supporting the developer scenarios and will continue to offer BIDS are part of the Express family.

Not to worry, many of the advances in the design surface are shared between both BIDS and the new Report Builder. There are also some advances in Reporting Services Express it self, which I'll share when we're closer to releasing a CTP of that.

While the discussion about Report Builder is done, the February CTP has just begun. You can download it from here. (Bottom of the page under the Instructions heading. It's a bit hard to find on the page so keep looking.) I look forward to your feedback.

 - Mike

Several questions have come up lately regarding whether SQL Server Express is an appropriate product to be used in a hosting environment.  Here are the answers:

·         User Instances (also known as RANU) available with SQL Server Express Edition, are NOT RECOMMENDED for hosting environments.

·         Shared hosting: SQL Server Express Edition is NOT RECOMMENDED for shared (multi-tenant) hosting as the scale limits and memory handling do not allow it to meet the requirements of such an environment.  SQL Server Standard Edition or Enterprise Edition are the best choice for shared hosting.

·         Dedicated hosting: All versions of SQL Server including Express Edition are RECOMMENDED for dedicated (single-tenant) hosting environments.  SQL Server Express Edition is a great way to provide a free copy of SQL Server with low cost Windows Server offerings.  Where customers require more advanced features or higher scale, Workgroup Edition, Standard Edition, and Enterprise Edition are all options.

·         Applications built on SQL Server Express Edition can be hosted in other editions of SQL Server.  There is a tool called the Database Publishing Wizard (it comes bundled with Visual Studio 2008) to help the export/import process.  If the Connection String contains a reference to User Instances, it needs to be updated (many sample applications and development tools include User Instances by default). There are several excellent post from Scott Guthrie that describe how to use the Database Publishing Wizard and how to post your ASP.NET application to a web host:

Recipe: Deploying a SQL Database to a Remote Hosting Environment

Update of SQL Server Database Publishing Toolkit for Web Hosting

Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

For hosters looking for more guidance on running SQL Server 2005 in a hosted environment, see the best practices article SQL Server 2005 Deployment Guidance for Web Hosting Environments.

Two posts in one day, I'm on a roll.

- Mike Wachal

- SQL Express team

I've gotten a number of questions recently asking how SQL Express uses resources during idle time, and how that behavior impacts the way SQL Express behaves "on first connection" after being idle for a period of time. While many of these questions have been related to web hosting, the behavior is a general behavior for SQL Express, so I'll answer in general terms.

SQL Express: It's SQL Server with a twist

SQL Express is SQL Server. This may seem an odd thing to say, but many people get confused on this point, so I'll say it again: SQL Express is SQL Server. We have introduced some scale limitations to SQL Express and removed some features from SQL Express, but it is the same code base as all the other SQL Server editions. There are some specific places where we have changed the way SQL Express behaves compared to other editions and one case where we've introduced a feature that is unique in SQL Express (more on that later). I won't go into the details of which features are in which editions, you can find that information on the Microsoft web site: SQL Server 2005 Feature Comparison. This post is about those behavioral differences that lie just beneath the surface.

Idle thoughts about Idle time memory usage

SQL Server, in general, tries to return query results fast. This makes sense in terms of being a centralized, multi-user data service. When there are a whole bunch of users simultaneously asking questions, it's good to be able to answer those questions fast. One of the many ways SQL Server manages to do this is by caching frequently used information in memory. (That's as technical I'll get, you'll have to look elsewhere for a deep, technical discussion of the ins and outs of memory usage in SQL Server.) With SQL Express we wanted to target a different scenario, specifically, we wanted to support being a data store for single-user applications. This presents a different set of challenges, for one thing, there are long periods of time (call it idle time) when no one is asking any questions. The idea of holding a whole bunch of data in memory doesn't make as much sense if no one is going to be using it, and it might even be bad, because there may be other ways the computer could use that memory. So we made a change in the way SQL Express behaves:

• When SQL Express is active it works just like any instance of SQL Server, data is cached in memory in order to improve the performance of subsequent queries.
• When SQL Express is idle it aggressively trims back the working memory set by writing the cached data back to disk and releasing the memory. This frees up memory for other applications to use.

  This behavior makes sense for a single user database engine - it supports higher performance when the application using the data is running, but once that application is shut down, memory is released to allow other applications to use it. I like to think of this as "being a good citizen" in the application community. There is always a catch when making this kind of trade-off though, and SQL Express is no different in this aspect. When SQL Express transitions from idle to active some of the memory that was released needs to be reclaimed; this results is a slight lag during "startup time" when you're first connecting to SQL Express. You can actually see this happening in the Widows Application Log, when you first activate SQL Express after some idle time, an entry is written to the log that reads like this:

  Server resumed execution after being idle 16056 seconds: user activity awakened the server. This is an informational message only. No user action is required.

  This is just telling you that SQL Express was idle and is now awake because some user activity woke it up. (When you think of it, it's kind of rude for users to keep waking up SQL Express without consideration for SQL Express's feelings. Maybe it's had a rough day and needs some rest!) This behavior is not configurable, it's just the way SQL Express is written. As mentioned above, this behavior change is unique to SQL Express, if you have need for your SQL Server to stay awake, SQL Workgroup or higher would be a more appropriate choice for your database engine.

  This (data)store will AUTO_CLOSE in 300 milliseconds

  Another minor change in SQL Express that has a lesser impact on perceived performance is the way the AUTO_CLOSE property of a database is handled. The AUTO_CLOSE property allows a database to be managed more like any other file in Windows by releasing the lock that SQL Server would normally hold on the file and allowing it to be copied, backed up, etc. This type of behavior is very important to supporting XCopy type deployment, which is a core piece of functionality needed to support Visual Studio ClickOnce™ Deployment. Supporting ClickOnce is something we wanted to do with SQL Express, so we modified the behavior of the CREATE DATABASE functionality in SQL Express in order to set AUTO_CLOSE to True for all databases. The results of this is that after 300 ms of inactivity for a database, SQL Express will close the database and release the lock on the file. It's important to recognize that Close and Detach are two different things. SQL Express still maintains metadata information about the closed databases, we just don't lock the file. When a request comes in that requires the use of a Closed database, we Open the database using the information stored in metadata. Opening a database that has been closed does not have a significant performance impact, but it can have some interesting side effects (and by interesting I mean negative) for certain kinds of operations:

• If you are running an iterative process that includes a cross-database query and the period of the iteration exceeds 300 ms, the external database your process access would go through a Close/Open cycle during every iteration. This can add up over a large number of iterations and become a large component of the process.
• If you have a process that polls a database at intervals, and that interval is larger than 300 ms, the database will go through a Close/Open cycle with every poll. In this case, the direct performance impact is negligible, but the fact that SQL Server writes an entry to the Windows Application Log every time a database is opened can result in your log filling up unexpectedly. (One example of this is if you have installed Reporting Services Express; it polls one of it's databases every couple of minutes.)

  Happily, you have some flexibility here to address the behavior within SQL Express. The behavior of CREATE DATABASE in SQL Express is not configurable, it will always set AUTO_CLOSE to True, but AUTO_CLOSE is just another database property, so you can change that property once the database has been created. Read the BOL topic ALTER DATABASE (Transact-SQL) for more information about changing the AUTO_CLOSE property programmatically. You can also change this property using SMO and in the Database Properties dialog in management studio. (Interesting Trivia: When you create a database in management studio, there is some post-creation processing that happens to set the database properties to match what ever is in the model database. Since all system database have AUTO_CLOSE set to False, even in SQL Express, this results in databases created in management studio to have AUTO_CLOSE set to False, even for SQL Express, unless you've changed the properties on your model database.)

  Net/Net - If you're using SQL Express as a standard multi-user server, you'll probably want to consider changing the AUTO_CLOSE property of your databases to False after you create them.

  What is a RANU?

1. An electric car built in Seattle and powered with recycled Starbuck's coffee cups.
2. A small marsupial native to Redmond, Washington that lives in the beards of SQL developers.
3. A user specific process of SQL Express designed to allow non-administrative users to use SQL Server as the data store for Windows Forms based applications.

   As much as I wish the answer were either 1 or 2, it is actually 3. RANU stands for Run As Normal User and is a feature that is unique to SQL Express. You've probably heard this feature referred to as User Instances. I'm not going to go into a detailed, technical description of User Instances, but will rather point you to the white paper on the topic, SQL Server 2005 Express Edition User Instances. I'll stick with the theme for this post and just discuss how RANU behavior impacts perceived performance and resource usage.

   If you didn't read the white paper (feel free to do so now, I'll wait) it is important to understand that RANU is a separate process of SQL Express from the parent instance and that it has some additional limitations beyond SQL Express:

• RANU supports only local connections via Shared Memory. (i.e. It's designed for single-user applications.)
• RANU supports only Windows authentication.
• RANU is a user specific instance, each user gets their own RANU instance that is not shared with other users. (i.e. The databases can not be shared between RANU instances.)

  There are three basic issues to be aware of when consider how RANU impacts resources and perceived performance:

• Since RANU is starting a separate user Process of SQL Express when an application launches, you have multiple copies of SQL Express running and using system resources. Most commonly this means there are two instances running, the parent instance and the RANU instance, for a computer with one user. (This could change, for example, on Terminal Services where you can have more than one user of the computer simultaneously.) RANU is tied to the User, not to the application, so while an application starting can result in a RANU instance being started, it does not shutdown the RANU instance automatically when the application closes. A RANU instance has a defined timeout value of 60 minutes; after 60 minutes without activity, RANU shuts itself down. The RANU timeout is configurable at the parent instances through the 'User Instance Timeout' setting.
• The first time you start RANU for a specific user, SQL Express has to make copies of all the system database that will be used by the RANU instance. Making copies of these files can take longer than the default connection timeout value (30 seconds if you're wondering) and result in a timeout error. Behind the scenes, the file copy will finish and the RANU instance will starte, it's only the connection that fails. If you try the connection again, it will succeed. I find that changing the connection timeout to 60 seconds in your RANU connection strings handles this "first use" delay. (Your mileage may vary.)
• On all subsequent connections to RANU for the same user (see #2 for the first time connection issues), there are two states, RANU may already be running having been started previously and not timed out yet or RANU may not be running, in which case it must be started. If RANU is not running, there is a short delay while the instances is started. I've not found this delay to be large enough to be noticeable within the context of other application load operations, and have rarely seen problems with the connection failing because of the lag, other claim there is a delay. Again, your mileage may vary, but changing the connection timeout to 60 seconds in RANU connection strings usually ensures a successful connection. The "start up lag" is part of the territory with RANU. You can consider the trade-offs of setting the User Instance Timeout to a longer period, thus reducing the number of occurrences of "start up lag", with the viability of keeping the dormant RANU instances running when it's not being used. (Note: RANU will trim it's memory usage when idle just like the parent instance of SQL Express, so you're really splitting hairs at this point.

  That's quite enough for now, it's been awhile since I've posted anything and I guess I got carried away.

  - Mike Wachal
  - SQL Express team

     

     

The Reporting Services team is developing a new report designer tool that we're considering including in SQL Server Express 2008 with Advanced Services. This new report designer has a different user experience that is more geared toward information workers than developers. You can take a look at a preview of the new designer by downloading the SQL Server Developer 2008 November CTP. Once installed, you can find it at Start | All Programs | Microsoft SQL Server 2008 | Reporting Services | Report Designer Preview.

So why am I telling you this?

I'm interested in your opinions of this tool and the idea of having the new Report Designer as part of SQL Express instead of BIDs. (Don't worry: BIDs will still be available in other SQL Server 2008 editions, just not in Express.) I think the look and feel of the new Report Designer will be easier to use for most SQL Express users while still offering all the major report design functionality that folks need.

The only features that won't be exposed in the new designer are related to more advanced development tasks, such as creating managed assemblies to use in your reports.

Reply requested

Register your opinion about this change by either posting a comment in this blog or by using the EMAIL link at the top of the blog to send mail to the team. We're interested in what you think.

Provide feedback

If you have feedback on the new Report Designer (features or bugs) you can post those through MS Connect, start at https://connect.microsoft.com/SQLServer. You will have to register on the site to gain access, but once you do you will find information about our Community Technology Previews (CTPs) and a place to post your feedback.

• Mike

  SQL Express team

SQL Server 2008 Express is now available as part of the November CTP - You can download it from the following link: http://www.microsoft.com/downloads/details.aspx?FamilyID=749bd760-f404-4d45-9ac0-d7f1b3ed1053&displaylang=en.

There is already a bunch of information out there about the new functionality available in SQL Server 2008, so I won't spend much time describing the features here. What I will do is document which of those new features will be available in the Express edition.

The following new features are available in SQL Express 2008:

New Datatypes

• Date/Time data type improvements
• Hierarchy ID
• Filestream Data
• Geographical Data

  DB Engine Improvements

• Large user-defined types
• Predictable query performance

  Programmability

• ADO.NET Entity Framework
• Language Integrated Query (LINQ)

  Productivity

• Declarative Management Framework
• Streamlined Setup

  For the November CTP we have only released the basic version of SQL Server 2008 Express. We'll be releasing SQL Server 2008 Express with Advanced Services and Management Studio Express in a future CTP. For now, if you want to use management studio to work with your SQL Express database, you should download SQL Developer edition, which is available from the same site listed above, and install the full version of Management Studio. This will work against SQL Express with no problems.

  Before you even ask, yes, the download package is pretty big; hey, it's a CTP! The download size will be reduced as we approach the final release so don't get too worried at this point, it's an interim step.

  Questions?

  You can provide feedback (bugs and feature request) on the SQL Server 2008 November CTP through the SQL Connect site: https://connect.microsoft.com/SQLServer

  You can post general SQL Server 2008 question in the appropriate SQL Server 2008 forum on MSDN: http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=428&SiteID=1

  You can post SQL Express specific quesitons in the SQL Server 2008 Express forum on MSDN: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1621&SiteID=1

  Have fun!

  -Mike

     

I've waited far too long to communicate this disappointing news to everyone, so here it is…

I'm not going to be releasing the SQL Express SP2 Bootstrapper manifests that I had discussed in a long ago blog posting. I'll spare you the sob story of my efforts to create this; suffice it to say that some requirements arose that complicated an official release and priorities changed over time. If you work in the software industry you know how it goes. So that brings us to…

My feeble backup plan

Most of the complications (ok, all of them) that I ran into were related to packaging up the manifests so they could be downloaded and installed. (Seriously, it's a long story, so don't ask.) The manifests themselves are pretty straight forward and only required some minor tweaks of the existing RTM manifests. There is nothing to stop me from sharing those updates with you in this blog and giving instructions on how to use them.

DIY SQL Express Bootstrapper

Technically, the bootstrapper, or the VS Generic Bootstrapper as I like to call it, refers to the technology created by the Visual Studio team to allow any developer to deliver specific sets of functionality as prerequisites that can be checked for and automatically installed by both ClickOnce and MSI based installers built with VS. The framework for building your own prerequisite is documented on MSDN in the topic Adding Custom Prerequisites. A more end to end treatment of the technology was printed in MSDN magazine back in 2004 in the article Use the Visual Studio 2005 Bootstrapper to Kick-Start Your Installation. These are both recommended reading for anyone delving into the land of creating a bootstrapper manifest. Feel free to take a look at those article now if you want, I'll wait until you're done.

The SQL Express bootstrapper is composed of five pieces:

• The product manifest that describes the non-localized pieces of the package
• The package manifest that describes the localized pieces of the package, such as the messages associated with specific error codes.
• A license file containing the SQL Express EULA.
• A custom executable that Microsoft created to check to see if SQL Express is installed or not.
• The actual SQL Express installation package.

  All five of these pieces is available to you, so you can create your own SP2 bootstrapper by creating a few directories and copying the files into the right place. These instructions assume a default installation of Visual Studio 2005 and also that you have not redirected VS to an alternate bootstrapper package directory.

  Creating the directory structure

  The default directory for bootstrapper packages is at C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages. Do the following:

1. Create a parent directory for the package named SqlExpressSP2.
2. Under SqlExpressSP2, create the localized resource directory named EN.

   Files in the SqlExpressSP2 directory

3. Copy the contents of the product manifest that I've modified for SP2 (located here) and create a file in this directory named product.xml.
4. Copy the file named SqlExpressChk.exe from the SQL Express 2005 RTM bootstrapper package (located at C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\SqlExpress) into your SP2 version.

   Localized files in SqlExpressSP2\EN

5. Download (or copy if you've got it already) the installer package for SQL Express SP2 from the Microsoft Download Center. (Note: This particular flavor of the manifest is specific for the 32-bit only install of SQL Express, which is named SQLEXPR32.EXE.)
6. Copy the EULA from the SQL Express install location and save it to the name license.txt in the EN directory. By default it is at C:\Program Files (x86)\Microsoft SQL Server\90\EULA and is named License_EXPR_ENU.txt.
7. Copy the contents of the manifest I've modified for SP2 (located here) and create a file in this directory named package.xml.

   Test your spankin' new bootstrapper package

   That's really all there is to do. You should now be able to launch Visual Studio 2005 and see SQL Server 2005 Express Edition SP2 listed in the prerequisites dialog.

   There's always a catch

   There are a few known limitations with the package that is created using this method, here are the gotchas:

• This set of manifests does not handle upgrades, only fresh installs.
• SqlExpressChk.exe is limited to only the default instance name, SQLEXPRESS. It won't detect other named instances.
• This set of manifests is specific to the 32-bit only installer package. If you want to use the WoW enabled package (for installing on 64-bit platforms), you would need to make some changes to the manifest. (This would be a good point to go back and read those MSDN articles about the Generic Bootstrapper if you haven't already.)

  Other bootstrapper solutions for SQL Express

  I know of at least one other description of a DIY SQL Express SP2 bootstrapper that was mentioned in a comment of the original post on the topic. Check out IGrocholski' blog post on this same topic. There are some difference between the approaches, particularly in discovery. You may also find other implementations by searching on the MSDN forums.

  I'm always happy to see community contribution, so feel free to share you own implementations with the rest of us by posting comments.

     

- Mike

Backup and Restore is an important part of any application, but when you're writing an application for an end-user, it becomes even more important that you do a good job of handling Backup and Restore in your code since you end-user will likely not know much about being a Database Administrator. Add to that the fact that we don't include SQL Agent or the Maintenance Plan wizard in SQL Express, and you have the perfect reason to roll your own solution for Backup and Restore.

SQL Management Objects (SMO) offers two classes with the likely name of Backup and Restore that can help you handle this in your application. These objects are documented in Books Online and you can even find a sample of their usage here. One thing you won't find in the BOL example is information about how user instances impact the usage of these classes. Luckily, you have this blog and the help of your friends on the SQL Express forum.

Mfriedlander started a couple threads on the forum related to backing up and restoring a database in a user instance. The trick with user instances is that the databases are typically auto named at runtime based on the path to the database file that is embedded into your project. VS and SQL Express work together using the |DataDirecotry| macro as part of the connection string to determine where the database is and how to dynamically name it. If you are using ClickOnce deployment, which is kind of the whole point of user instances, there are a number of things that will cause the location of the database to change, which means the database name will change over time. (This also has the result of not allowing you to give you database a static name using either Initial Catalog= or Database= in your connection string because the changing location of the database would cause a naming conflict, but that's a different post.)

For the backup scenario, the workaround is fairly straight forward, you need to connect to the database using the VS created connection string and then return the name of the database from the connection. The restore version proved a bit more interesting. You can use the same trick of connecting to the database using the VS created connection string to get the database name, but that connection to the database causes a failure when you attempt to Restore because SMO cannot get an exclusive lock on the database. The solution turns out to be straight forward, simply change the database context of the connection you've opened with the ChangeDatabase method of the Connection object. I've created a C# sample that creates a full backup of an embedded database and then calls Restore on the same database. If you'd like to see the similar operation done in VB.NET, check out the forum posts that I've linked above.

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;



namespace CreateRANU
{
    class Program
    {
        static void Main(string[] args)
        {
            BackupDatabase();
            RestoreBackup();
        }

        public static void BackupDatabase()
        {
            string sConnect = Properties.Settings.Default.BackupConnectionString;
            string dbName;

            using (SqlConnection cnn = new SqlConnection(sConnect))
            {
                cnn.Open();
                dbName = cnn.Database.ToString();

                ServerConnection sc = new ServerConnection(cnn);
                Server sv = new Server(sc);

                // Check that I'm connected to the user instance
                Console.WriteLine(sv.InstanceName.ToString());

                // Create backup device item for the backup
                BackupDeviceItem bdi = new BackupDeviceItem(@"C:\AppDataBackup\SampleBackup.bak", DeviceType.File);

                // Create the backup informaton
                Backup bk = new Backup();
                bk.Devices.Add(bdi); 
                bk.Action = BackupActionType.Database;
                bk.BackupSetDescription = "SQL Express is a great product!";
                bk.BackupSetName = "SampleBackupSet";
                bk.Database = dbName;
                bk.ExpirationDate = new DateTime(2007, 5, 1);
                bk.LogTruncation = BackupTruncateLogType.Truncate;

                // Run the backup
                bk.SqlBackup(sv);
                Console.WriteLine("Your backup is complete.");
            }
        }

        public static void RestoreBackup()
        { 
            string sConnect = Properties.Settings.Default.BackupConnectionString;
            string dbName;

            using (SqlConnection cnn = new SqlConnection(sConnect))
            {
                cnn.Open();
                dbName = cnn.Database.ToString();
                cnn.ChangeDatabase("master");

		        ServerConnection sc = new ServerConnection(cnn);
                Server sv = new Server(sc);

                // Check that I'm connected to the user instance
                Console.WriteLine(sv.InstanceName.ToString());

                // Create backup device item for the backup
                BackupDeviceItem bdi = new BackupDeviceItem(@"C:\AppDataBackup\SampleBackup.bak", DeviceType.File);

                // Create the restore object
                Restore resDB = new Restore();
                resDB.Devices.Add(bdi);
                resDB.NoRecovery = false;
                resDB.ReplaceDatabase = true;
                resDB.Database = dbName;

                // Restore the database
                resDB.SqlRestore(sv);
                Console.WriteLine("Your database has been restored.");
              }        
        }
        
        public static void CreateDatabase()
        {
            using (SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True;Connection Timeout=60"))
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand("CREATE DATABASE forumTest1", cn);
                cmd.ExecuteNonQuery();

                SqlConnection cn2 = new SqlConnection(@"Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDbFilename=|DataDirectory|\forumTest1.mdf;User Instance=True;Connection Timeout=60");
                cn2.Open();

            }        
        }
    }
}

Final Note:

This example performs a full backup and restore of the database. If you're doing a full backup because your original database is totally lost the logic to retrieve the database name is going to fail because you won't be able to connect to the database (which has been lost or corrupted) to get the name. It's an interesting logic question as to figure out how to determine the right way to restore a database for a user instance when you can't connect to the original to get the right auto name from the project. I have a few ideas, but I'm interested in how others might do this.

- Mike

Just a quick not to let you know that the SP2 update for SQL Express, SQL Express with Advanced Services and SQL Express Toolkit are being published to Microsoft Update today. These are automatic updates and will apply to computers based on your Microsoft Update settings for automatic updates. (i.e. Notify, Download and Notify or Download and Install)

The updates will also be part of WSUS so that IT Administrators have control over deployment of these updates throughout an Enterprise.

When applied through Microsoft Update or WSUS, the Express SP2 updates will discover the list of installed features on an instance and update those features appropriately. Additionally, Microsoft Update will detect and update all instances of SQL Express at the same time. This is different from the UI version of the Express SP2 update which must be run seperately for each instance of SQL Express installed on your computer.

-Mike

It's been a long road filled with many shouts of "Are we there yet?", so I'm happy to say that yes, we're there now. SP2 has finally be released. You can find the SQL Express SP2 bits on the SQL Express download page along with several other related downloads. As of this writing, we were having some problems with our publication system, so you may see that the page still says SP1, but the Download links themselves are updated and will actually download SP2. We'll get the page updated as soon as possible so it actually says what you're downloading.

The most significant thing in SP2 for Express is that we have added the ability to provision the installing user as a SysAdmin on SQL Express, which is a requirement when you are running on Windows Vista with UAC turned on. You can read more about this in my previous blog post on the topic.

I mentioned quite awhile ago that we would be putting SP2 on Microsoft Update. Organizing this takes a few extra weeks, but you can expect that the SQL Express SP2 update will be offered via Microsoft Update by the end of March if not sooner. When updating from Microsoft Update, all instances of SQL Express will be updated together rather than having to do them one at a time as you have to do with the UI based version.

Finally, I'm working on creating a new manifest for the VS Generic Bootstrapper that will allow developers to embed SQL Express SP2 with their applications rather than the RTM version that ships with VS 2005. I know many people have been waiting on this update and I hope to have it out by mid-March. I'll post more information in the blog when it's available.

- Mike

If you've been reading this blog or the SQL Express Forum you've undoubtably seen me write about SQL Server 2005 Compact Edition (SQL CE) as an alternative database for use when writing client applications that require local data storage. SQL CE is the evolution of SQL Mobile, with support added for Windows desktop operating systems. The SQL CE team has been working hard over the past months to do all the right things to make this a great product for desktop database development and they've just released! I'll let you explore to find the details; if you're developing applications that require local storage, be sure to check out SQL CE.

SQL CE Product Page
SQL CE TechNet Page
SQL CE Developer Center

- Mike

The SQL Express team has been working hard to increase the offerings the help you learn and use SQL Express. Be sure to check out these new offerings.

Mike

SQL Server Express Registration Benefits Update

We recently update the 'Welcome' e-mail sent to SQL Server Express registered users to include many helpful new links and resources. In addition, we've refereshed the registration benefits to include an e-Book chapter from SQL Server 2005: Step by Step on how to develop SQL Server Express applications. Registered users can simply go to the Registration Benefit Portal to download this and other benefits for free. We are constantly adding new benefits, so be sure to visit the benefit portal often!

Five new SQL Server Express Absolute Beginners Videos now available!

In our continuous effort to help new developers get better acquainted with SQL Server Express, we've released five new learning videos to bring the total number of videos to thirteen - that's almost nine hours of video-based instruction! The Absolute Beginner video series is designed for new users interested in learning the baics of how to create, manage and connect to SQL Server Express databases through self-paced and on-demand learning resource. The new videos include:

• Using SQL Server Management Studio Express
• Getting Started with Reporting Services
• Embedding, Packaging and Deploying SQL Server Express Reporting Services
• Creating and Using Stored Procedures
• Enabling Full-Text Search in your Text Data

Select your starting point based on your skill set by visiting the MSDN SQL Server Express Absolute Beginners Video Series page.

Owen Thomas wrote an article for the Business 2.0 secton of CNNMoney.com on December 15,2006 that made some inaccurate claims about SQL Express compatibility with the upcomming Windows Vista operating system. Mr. Thomas wrote that you "can't run the current version of SQL Server" on Windows Vista. This is not true, SQL Express SP1 is fully supported on Windows Vista.

We have documented this level of support at a number of locations on the Microsoft web site, including the System Requirements page and in our 'SQL on Vista' FAQ page. The fourth Q/A pair on the page states:

It is true that there is a known issue related to how Vista User Account Control impacts the ability to connect to a default installation of SQL Express SP1, but I've documented the way to work around that problem in here. (For most deployed application this issue will not be a problem as they already create explicit Logins to access the database rather than rely on the default Logins. See the post for more information.) I've also discussed how the installation process in SQL Express SP2 has been modified to help manage UAC in this post.

Just to reiterate...

SQL Express SP1 will run on Windows Vista.

Microsoft supports customers running SQL Express SP1 on Windows Vista.

If you have questions about running SQL Express on Windows Vista you should feel free to post a question in the SQL Express forum or to contact me through the e-mail link in this blog.

Mike Wachal
SQL Express team

Many of you are working with User Instances, sometimes without even realizing it. Briefly, a User Instances is special process of SQL Express that can be started by an application at runtime and allows SQL Express to emulate some of the characteristics of embedded databases. User Instances were added to SQL Express in order to better server developers creating ClickOnce applications in Visual Studio 2005.

As long as you work completely within Visual Studio, you'll likely never even notice that anything special is going on. Visual Studio gives you the data tools you need to handle most tasks required for creating a database that sits behind an application. In some cases, you might want to move to a richer tool set, such as SQL Server Management Studio or SQL Server Management Studio Express, which is when you're going to hit a road bump. What road bump you ask?

1. You can't find your database in Management Studio.
2. You can't attach to your database in the location where Visual Studio creates it.
3. If you manage to attach the database in Management Studio, your application can not longer user it.

There are a myriad of solutions to each of those problems individually, but the real solution to all of them is to learn how to connect Management Studio to your User Instance so that you can work with the database using the same instance of SQL Server that your Visual Studio application is using. As always, there are a couple things you have to be aware of when working with User Instances.

• User Instances are only supported in SQL Express.
• You can not start a User Instance from Management Studio. A User Instances is spawned by the parent instance of SQL Express when an application makes a special connection to the server.
• If the User Instance isn't running, you will not be able to connect to it from Management Studio.
• User Instances are only accessible through the Shared Memory connection protocol, and are only exposed through a Named Pipe.
• Each user on a computer has their own User Instances that is unique to them.

The rest of this post describes how to start a User Instance, how to determine the State and Pipe Name for a User Instances and how to connect to a running User Instances using Management Studio.

Starting a User Instance

A User Instances is automatically created and started by SQL Express when an application makes a connection to the parent instances using a special connection string. Specifically, the connection string must contain the keyword "User Instance=TRUE". Read the white paper if you're interested in the gory details, for this post all you need to know is that SQL Express knows what to do with that connection string keyword. When you add a database to your Visual Studio project, a connection string is automatically generated that contains the right information to start the User Instance when the application is run. Since it is most common that you will want to work with your database while you are working with your application, the easiest way to start your User Instances is to start the application using Start Debugging or the F5 key. If you don't want to bother opening Visual Studio and loading your application, you can download a tool called the SQL Express Utility that is capable of starting your User Instances. SSEUtil is a tool written by the Visual Studio team to help troubleshoot User Instance issues, you can read more about it in the read me file that is installed with the utility.

Determine the State and Pipe Name of a User Instance

You can get information about the User Instances on a machine that are associated with a specific instance of SQL Express by using the dynamic management view sys.dm_os_child_instances. I say "associated with a specific instance" because User Instances are unique to both the user than starts them and the instance of SQL Express where they are started. For example, if I have two instances of SQL Express on my computer named SQLEXPRESS and MIKESOTHERINSTANCE, I could actually have two separate User Instances running for me, one for each parent instance. Back to the DMV. There are a number of columns available from this View, but I'll focus on three of them and let you explore the rest yourself. The three columns of interest are owning_prinicipal_name, instance_pipe_name and heart_beat, as in:

SELECT owning_principal_name, instance_pipe_name, heart_beat FROM sys.dm_os_child_instances

Here is the purpose of each column:

owning_principal_name: The name of the Windows User account that owns the User Instance in the form MacineName\UserName or DomainName\UserName.

instance_pipe_name: The Named Pipe reference to the User Instance, which will be important in the next section.

heart_beat: Denotes the State of the User Instance as Alive if it's running or Dead if it is not running.

Connecting to a User Instance in Management Studio

Now to the point, go ahead and start Management Studio and connect to your parent instance of SQL Express, it's probably named SQLEXPRESS. Open a new query windows and run the query from the section above. If you've never created a User Instance before you won't see any results returned, but if you have, you'll see something like this:

As you can see, I have two User Instances on my computer, neither of which is running at the moment. I can see that the first User Instances belongs to me, while the second User Instance belongs to OtherUser, which is a local user on my computer that I use for testing. The Pipe Name is interesting because that is how I'm going to connect to the User Instance once I start it. For this example, I'd just use SSEUtil by opening a Command Prompt to the directory where I've saved the tool and running SSEUtil -L. If you've read the read me file, you know this command line will start the current users User Instances and list all the databases currently attached. The important thing is that my User Instances is now started, and if I ran the DMV again, it would now look like this:

Now that my User Instance is alive, I can connect to it in Management Studio just like any other SQL Server.

1. Copy the Pipe Name out of the query results.
2. On the File menu, click Connect Object Explorer to open the Connect to Server dialog.
3. Paste the Pipe Name from step 1 into the Server Name text box.
4. You have to use Windows Authentication, that's the only authentication supported by User Instances.
5. Click Connect.

You will see a new Server show up in your Object Explorer that represents the User Instance. You can work with this User Instance just like any other SQL Server. Since this is the same User Instance that is being used by your Visual Studio applications, you won't have any conflicts between having your embedded database open in the application and opened in Management Studio.

Rather than running the DMV every time you want to connect to your User Instance, it's a real time saver to add your User Instance to the Registered Servers list once you discover it's Pipe Name. When SQL Express creates the User Instance the first time a random Pipe Name will be created, but once it's created, that same Pipe Name will be used every time the same user starts the User Instance. Once you've added your User Instance to the Registered Servers list, you can connect to it by just double clicking it, as long as it is already running. Have fun!

Mike

Now that SQL Server 2005 SP2 has been released I thought I should make good on my promise to explain more about how we handle Windows Vista User Access Control in SQL Express SP2. I explained earlier that UAC works by removing the administrator ACEs from your User Token when you are a member of the Builtin\Administrators group. This effectively limits your permission to connect to an manage SQL Server because we assign permissions based on membership in the Builtin\Administrators group. To address this in SQL Server SP2, we created two ways to add a user directly to the SysAdmin Server role during the installation process. One of these ways is unique to SQL Express, the other is available for all editions of SQL Server SP2, but is exposed differently for SQL Express.

Adding the "setup user" to SysAdmin

In order to address the unique needs of SQL Express, we added a new option into the setup process that allows you to automatically add the user who is running setup into the SysAdmin Fixed Server Role. This option is turned off by default, so you have to take a specific action to make this happen. If you don't take this action, the default install of SQL Express SP2 will behave exactly the same as SP1 does with regards to UAC. If you miss setting the option during installation, you can use the provisioning tool that I describe later to accomplish the same thing.

Using the Setup UI - We've added a new checkbox to the Configuration Options page of the Setup UI with the caption 'Add user to the SQL Server Administrator role'. The checkbox is unchecked by default. Checking this box will result in a Login being created for the user account that is running Setup.exe and that Login being associated with the SysAdmin role. This is how we provide permissions for the Builtin\Administrators group already.

Watch out! When you start the installer for SQL Express SP2 on Windows Vista with UAC enabled you will be asked to "elevate" the process and give it administrative privileges. If you are already an administrator on the computer, you can elevate the process using your own account with your Admin Token restored. Windows Vista also supports the ability to allow normal users (e.g. non-administrative users) to provide the credentials of a different user who has administrative privileges in order to elevate the installer. If you do this, Setup will be running as the administrative user, not as the normal user who is logged in. In this case, a Login would be created for the administrative user, not the normal user.

Using the Setup command line - To support embedding, we've added a new command line argument that results in the same behavior. The argument is ADDUSERASADMIN with the following usage:

To add a Login for the user running setup:

ADDUSERASADMIN=1

To not add a Login for the user running setup:

ADDUSERASADMIN=0 (default)

Using the SQL Server User Provisioning on Vista tool

All editions of SQL Server SP2 will include the SQL Server User Provisioning on Vista tool. There are two ways to launch this tool:

• On non-Express editions, the tool can be launched from a link on the last page of the Setup UI.
• For all editions, including SQL Express, you can launch the tool from within the SQL Surface Area Configuration tool. I'll discuss this part since it applies to SQL Express.

When you launch SQL SAC in SQL Express SP2, you should see a link that reads 'Add New Administrator'. Clicking this link should launch the provisioning tool and allow you to specify the user you want to add to the SysAdmin role and the SQL instances that you want to make the addition to. You can specify more than once instance at a time, but only a single user at a time. For SQL Express, you will only see SQL Server instances listed, but for non-Express editions other SQL services will be listed as well.

Note: I've found in recent builds that the link in SQL SAC is not working correctly, I'm not sure if this is the case in the November CTP or not. If the link in SQL SAC does not work, you can launch the provisioning tool manually by double-clicking the file located at:

C:\Program Files\Microsoft SQL Server\90\Shared\sqlprov.exe

When not to use this

If you're application is already creating specific Logins (whether SQL or Windows Authentication) as part of your installation process then you will likely not be impacted by Windows Vista UAC. Use of SQL Express provisioning mechanism or the provisioning tool is targeted at installing SQL Express for use in an administrative environment, in fact, both SQL Setup and the provisioning tool require the user to have administrative privileges on the computer. When you are installing SQL Express to be used as part of an application, it is a best practice to create specific Logins and Users with appropriate permissions for the use of your application.

- Mike