Upsizing to SQL Server from SQL Server CE 4 Beta

One of the great advantages to SQL Server CE 4 is the migration path from an embedded database directly to the full SQL Server. This is a tremendous benefit for anyone who wants to easily distribute a "Lite", "Express" or "Start Small" edition of their application, but allow for the upgrading to a full database in the future. Both schema and data are migrated in this process, and in the beta version this is an all-or-nothing process. You can't select what you want to migrate.

With WebMatrix beta and SQL CE 4 beta, the process is insanely easy. For this sample, we'll use a database I created for my Pittsburgh.NET user group presentation last week (you can find my presentation at http://c1.ms/b1wXs2). If you need some help creating a database, check out my article at http://c1.ms/9Qmt1C.

To upsize a database, open the site, click the Databases button and select the database you want to upsize. It's important to note that when you expand the database, only Tables are displayed. SQL CE 4 beta does not support views or stored procedures. Also, the database name indicates the .sdf extension.

When the database is selected, the Migrate button in the ribbon is enabled. Clicking this button starts the process.

If the database exists, you'll be prompted to overwrite the existing tables. This will only overwrite tables with the same names. Tables in the target database that aren't in the source will be left alone, and tables in the source but not in the target will be added to the database.

By default, the migration targets SQL Express. I prefer the full version of SQL Server I have installed, so I'll target that. Set a name for the database, and make sure the user account has enough privileges to create databases. Then click OK. The connection will be tested, and the status bar at the bottom will subtly indicate the progress.

When the migration is complete, it will look like you're back where you started. However, there have been some changes made to your website. First, the connection string has been changed to your new SQL Server database. You can see this by expanding the database and seeing both Views and Stored Procedures can be listed. The database name no longer indicates the .sdf extension.

Also, the SQL CE database has been named *.backup.

Finally, you can open SSMS and enumerate the databases and see your new database there.

That's it!