So there’s not a whole lots of stuff on the interweb that gets you started with drives and SQL Compact 4 is only just out into the open. My need was for a lightweight database for a sample app I’m building with Chris Klug for our session at Tech Ed New Zealand.
I thought I’d run quickly through how I got things going.
Getting the BitsI grabbed the bits using the Microsoft Web Platform installer. I wanted two things. 1) SQL Compact 4 CTP1 and; 2) The Web Matrix tool.It’s hopefully fairly obvious why I needed SQL Compact. The reason I wanted Web Matrix is because it’s currently the only tool I could find that will allow me to create and edit SQL Compact databases.
I’ll be using Visual Studio for much of this post; I’m guessing y’all know how to get your mittens on that now.
Creating a DatabaseTo build our database we’ll be using Web Matrix. It doesn’t have a specific database designer that is independent of the site editor so we’ll need to create a site and then create it in the context of our site.
We’ll start from a template.
…and create an empty site.
Choose Databases from the left hand nav.
Choose to create a new database from the ribbon
Double click to select tables. And right click to add a new table
Create a basic table definition with a ContactID and ContactName. Use the Ribbon controls to create new columns. Use a BigInt identity column for the ID and make it the Primary Key.
Save the table as Contact
C:\Users\Chris Auld\Documents\My Web Sites\Empty Site\App_Data
We need some data in our database. We could ahve used Web Matrix to do this but I wanted to show another tool that allows us to execute raw SQL against the DB as it may be more suited to some developers.
The tool in question is the SQL CE Toolbox (you’ll need the latest version which is a standalone application)Grab it from here: http://sqlcetoolbox.codeplex.com/Don’t just install the VIsual Studio add-in version as at the time of writing it doesn’t support the v4 sdf format.
Open the tool and right click to add a new connection
Open the database file and test the connection
Expand the tree to show our Contact table. Right click and choose ‘Script as Insert’. This will create a template insert script for us.Enter some sensible (or stupid if you prefer) values and then execute the query. I didn’t attempt to insert the identity column and so removed that from the query.
Before we leave this tool click the Estimated Plan toolbar button. As long as you have SQL Server Management Studio installed it’ll open that tool and hand over the query plan text.
That’s right folks. That is a graphical query plan graph of the query for my embedded database. That’s pretty damn cool!
Getting Your Database Into Windows Azure Storage
Being a database we want our *.sdf file sitting in a WIndows Azure drive. There are a bunch of ways we could do this:
- We could put it into our service package, create a drive on startup, copy to the drive and then do a lazy check each time we start so it’s only done once.-We could create the whole VHD on the client machine and upload that to storage. This removes any of the once only init code from our application. THis is the approach I chose to use
We need to create a VHD. Hooray for Windows 7- this is trivial. If you are a hard-ass like Steve Marx then you can use a batch command to do this but you all know that I’m not really a hard ass so I’ll do the draggy droppy clicky clicky thing.
Open Disk Management. I’m a fan of the search box in the start menu- type ‘Disk Management’ and then select ‘Create and format hard disk partitions’
Use the Action menu to Create VHD. Stick it somewhere useful like the desktop as we’ll need to grab it later. You’ll want to choose a fixed size- I chose to make it 100MB. You’ll only pay for the actual data you store on disk (not the empty stuff) but more on that shortly.
Once created you’ll need to Initialize the disk with an MBR
.. and Format as NTFS
The drive will now be available in Windows Explorer and we can copy our *.sdf file across from the My Web Sites directory to our new VHD
Once we’ve copied it over we can go back and Detach the VHD
Now we can upload the VHD to Blob storage. Before you crack out your favourite Azure storage client (I like Cloud Studio from Cerebrata) you’ll need to want to check that it supports sparse Page Blob uploads. Cloud Studio doesn’t so we’ll use the VHDUpload Tool from here: http://blogs.msdn.com/b/windowsazurestorage/archive/2010/04/11/using-windows-azure-page-blobs-and-how-to-efficiently-upload-and-download-page-blobs.aspx I took the liberty of building the tool: VHDUpload
More in the Part 2 of this post tomorrow.
a@href@title, b, em, i, strike, strong, u