Video Transcript
Welcome to session two in this three part series where we talk about SQL Server 2008 Compact and Express Editions. This time around we’re going to focus on deployment and management and what we’re going to focus on here is deployment of SQL Server Compact because that is something that is probably going to be quite different from what most of you are used to in terms of deploying a database since it is typically a per user database. And lots of times is deployed with the application itself and then lives on something like a laptop or a handheld device and what not.
So what we’re going to do is we’re going to talk a little bit about how to work with ClickOnce, making sure that everybody’s up to speed on that. I’m going to show you a quick little example as well of how it actually works, how you configure it, how it’s being deployed. And then we’re going talk about security and how we could deal with encrypting passwords, if we use passwords to further secure our database. And then we’ll also talk a little bit about management; I’ll show you the things that you can do in terms of managing the database when it comes to working with the Compact Edition.
Again, the reason why I want to focus on SQL Server Compact, the SQL Server CE 3.5, is because in terms of managing and deploying SQL Server Express, it is fairly similar to working with other databases, standard enterprise and what not. And there are several other sessions located at ISVInnovation.com, where I talk about those types of databases, multi-user databases in more detail.
SQL Server 2008 CE Review
So, let’s go ahead and just a quick little recap here about SQL Server CE or SQL Server Compact. Again, the name means that it can be a little confusing but when we talk about SQL Server Compact 2008, SQL Server CE 3.5, it’s the same thing. Just to confuse the innocent, we have lots of different names for the same database version.
So again, it is a very small database, very small footprint and that’s often is embedded within the application itself, runs within the same process as the application, has a subset of SQL Server types. And SQL Dialect, meaning that we don’t the full blown TSQL language to work with , something that is really great when you need to work with mobile embedded devices. A couple of examples of usages windows media center and soon Windows Media Player, give you some real examples of where this is being used.
And this is something that we can use to either work with when I need to have an offline data storage available somewhere so I need to store my data offline and then at some point, we’re going to get connected and then we’re going to synchronize to a centralized repository. And the steps for dealing with synchronization and more about developing applications and working with the ADO.NET libraries for SQL Server Mobile is something that we’ll discuss in the third session.
Good Things to know about ClickOnce
So ClickOnce is something that I want to focus on here first. I just want to make sure that everybody is on the same page in terms of what is ClickOnce. It’s something that’s been around for a while. It was introduced with the .NET 2.0 platform. It is something that allows you to do non admin installs which is very important, especially with the way operating systems such as Vista work where everybody is logging in as a standard user and we have to be able to install applications. Not always to the program files folder but if we are acting as a non admin, we want also to be able to install the application into my local profile folder. And that is exactly what Click One is doing. It’s also something that allows us to deploy things over the web so we don’t have to distribute MSI files or set up that exe files or DVDs or what not. But we can simply just point people to a URL that they click on and then they’re going to initiate the actual download. It’s completely declarative.
We have no custom actions as we have with MSI packages, it’s X copy. And it’s something that is quite easy to set up and manage in terms of versioning. Because when we deploy something over the web, we need to make the application aware of the fact that there might be an new version out so that is something that is built in to the Click One framework. Completely free of dealing with the registry again, it is because it allows us to be non-administrators and we don’t’ need to act just a registry, we’re dealing with program files folder, everything installed on a user per user basis. If we have three users, I don’t know what this would be the case, but let’s say we had three users accessing the exact same desktop, the log in with different account, they installed the same application, there would be three versions of that installation.
Demo
So, let’s go ahead and do a quick little example of how to work with SQL Service CE or SQL Service Compact Edition and also dealing with ClickOnce. So what I’m going to do here is I’m going to build a very super simple application from scratch. The purpose here is not to deal with lots of writing code but just show you how you can embed a database within the application and then focus on ClickOnce.
So, I’m going ahead here and create a new project, I’m going to create a new Windows forms application. I’m just going with the defaults, there it is. Again, we’re not going to create something that looks super exciting. Now from here, from the server explorer and by the way, if you can’t see that, what you’ll do is you go up in, under View. And then from there, you have the ability to say that I want to look at the server explorer itself, ‘cause typically it doesn’t open up when you initially open up Visual Studio. So, let’s go ahead and go under View and then we’ll look into server explorer and there it is.
So what I can now is I can either add a connection to an existing SQL Server CE database. You can see here that I can change my data source so if it doesn’t show up, SQL Server Compact 3.5 by default and you can obviously change that right here. And I can either connect to an existing one, and this is going to be just browsing to an SDT files so we’re not connecting to a database instance here. So if you do have SQL Server, the framework installed, then you can go to Program Files Compact Edition 3, 5 and samples and you have a sample database there.
I’m going to go ahead and just create a new database, show you how that is being done as well. I can also just say “create”, it’s going to ask me for a location, where is this going to be stored? And I’m just going to call this one, MyDB, add an .sdf file extension to it. I can go ahead and add a password here if I want to. We can do that, we can go ahead and we can pick the encryption mode if we want to. I want to go ahead and just go with the no password. We’re going to say okay. I’m going to continue with the blank password and just say okay again.
So now we have the database here and I can go ahead and I can add some tables to it.. We’ll say create tables and I’m just going to create something really simple here. So let’s say we want to create them and please database, and we have a name, column. Again, I’m not going to spend a ton of time architecting this database using best practices. I’ll just show you real quick that obviously you can go in here and change the data type. Notice the data types available. We don’t have the exact same list of types as we would if we work with, let’s say Express or the Enterprise Edition. So we have a name there. We’re going to have a employee types, full time or part time and what not. Let’s say we want a location column as well and then we’ll go ahead and we’ll say okay, and there is the database itself.
So I want to go ahead and add that to this application that we have over here. So what I want to do is I want to go ahead and I’m going to add a data grid view. And then I’m going to connect to that database. I’m just going to go through the wizard here and I’m going to, in the drop down list, it’s going to show my DB.SDF. It shows that they’re there just because I’ve added it over here already. If I wanted to I could go into new connection and I could create the database here as well. So it doesn’t have to be created before you actually create the connection.
Now, what it’s going to ask me is-- the database that you’re working with is not in the current project-- do you want copy this to your current project and modify the connection string? And that is a good idea ‘cause it’s going to be a lot easier to manage. It’s also going to be a lot easier to deal with deployment. So, we’ll add my one table that I have in there, we’ll click finish. Now you’re going to see that Visual Studio is going to generate quite a bit of code over here. Again, we’re not going to focus on coding here at all, so we don’t really care about what’s being added there. Now go ahead and dock this in the container, just to make sure that it takes up all of the space in there.
Now go ahead and dock this in the container, just to make sure that it takes up all of the space in there. I just want to show you one quick thing here. Notice that it does add this assembly system data SQL Server CE and if you look at that real quick in Object Explorer again --in the last session we’ll talk more about development in detail – I just want to show you here that inside of here, you have the API, the data access API that we would use when we work with SQL Server CE. So we wouldn’t work with the traditional ADO.NET API that you would work with, that you would use with your winforms or webforms app when you go against a multi user database express standard and what not. But this has its own API that is available.
So that’s all good, let’s go ahead and run this, make sure that it compiles, there it is. Again, not the most exciting in the world but there it is. There’s obviously no data in there and I haven’t added any code that allows me to add data. I can type in stuff here if I want to. So I add in myself. I’m full time and I live in Southern California. But as soon as I close this down, since we haven’t added any code to persist the data, it will be gone. But again, in this particular session, we don’t care about that. We just pretend that it’s, you know, the application is there, it’s done .
What we want to do now is that we want to figure out, okay, how do I deploy this to all of my users, making sure that the database is embedded as well. So what I do is I go to project and then go to My Properties for this application and then we’ll look under publish. You could see that I have the ability now to deploy this to a web location. So I do have on my C drive, the root folder for that web application. So I have an application that I deployed previously, so what I’m going to do is I’m going to deploy this new application and it’s running on port 9191. And there’s quite a few things that we can do here. We can deal with prerequisites, e.g., what are the prerequisites for this particular application? I’m compiling this using .NET 3.5 so, obviously that’s going to be a prerequisite but notice that you can have previous versions as well.
We can specify that we need a compact framework as well since we using the Compact Database Express Editions Service Pack 2. So notice they say that we could also actually deploy SQL Server Express as ClickOnce. And when I check things off here, it’s actually going to ask me: well how do you want to get these components? We can download these from the vendor’s website. It can be from my own location or from some other specific location.
We can also specify how we deal with applications so, this is obviously the first version but I can say that every time this application starts up, I wanted to go out and check for newer versions and where is that update location? Is this that same location as I’m using right now? Or is the update location somewhere else? I’m just going with the defaults here, right now. And we’re going to deploy version 1 and I’m going to go through a published wizard that we’re going to kick off . I’m going to say that I want to deploy this to port 9191, and I’m going to make this available online or offline, meaning that I’m actually install the application as well. So even if they’re not connected to the network, they will still be able to run the application.
So, now it’s going to go about deploying the application. And if we go ahead and look at the file system here, just to show you what’s actually being deployed, you can see that it’s going to be an HTML file. Then we have a manifest right here. Remember before we said that ClickOnce is all based on manifest. Here is the deployment manifest. There is also a setup.exe. But the one that they kick off from the website when they say install, you could see it’s that setup.exe file that you also see inside of here. But all the setup instructions is in the manifest file right here.
We also have application files. So this is where we find the application file itself. You can see we have another manifest. We have some information in a deploy file for the database, the .sdf file. If we’ll go ahead and look at this, so we’ll open this up with good old Notepad, you can see that what we see inside of data deploy file that is not an XML file. That is the actual database itself. You can see it mostly, well, it’s all just binary garbage in there. But that’s what we find in the deployed file. But the key thing here is that you don’t really care about all of those files, meaning that they are being generated for you. This is what the end user would see. This is the only thing you need to point the end user to, this particular location, they get the prerequisites. I’m going to click install and let’s say we’re going to run the setup.exe. I haven’t signed this; I don’t have valid certificates. So that’s why we get this unknown publisher. I do trust my own application so I’m just going to go ahead and say run there. And then we’re going to go ahead and install the application itself and there it is. It’s now up and running.
So if I close this, and then we’ll go ahead and go to programs, you can see that under here, there is WinForms Application 2. So even if I’m going to look connected to the network, I can still open up the application. I can access the application. I can write to the database because it is deployed locally as well. I don’t need to maintain a consistent network connection to work with this particular application.
Now, if I go in and I make some type of a change here, so let’s say that I’m going to undock from the parent container, we’ll go in and we just add some random component here. So let’s say that I want the open file dialogue, for example, go in and we’ll just make some quick changes to that. Actually, the file dialogue is not going to be that exciting, let’s actually add something else. Let’s say that we want a menu script, that’s slightly more exciting. Let’s say we have File, then we have an Open, then we have Save Dialogue and I’m not going to add any functionality but just some changes to the application. Again, we’ll run that and make sure that it runs properly, there we go. We have some other options.
So we have a new version of the application, then the next thing I’m going to do is I’m going to go back into the properties here. You can see that now we have a new version, we have a new build version and I can obviously go in and I can change this as well. It’s also automatically going increment to reverse revision with each published so if we’ll save that and then we’ll go ahead and we publish this. I’m not going to go through the wizard, I’m just going to pick the same options that we had before. Publish that, it’s going to be new version, you can see that is now version 2001. And if I go ahead and open that up on my desktop again, you could see that it’s going to check for updates and it sees hey, there’s a new version available and I want to actually download that. So now what’s running locally on my machine, it’s going to be Version 2. Again, if I open this up again, you could see Version 2 is not the one that is currently installed on my particular machine.
Now one thing that you obviously have to be careful with here, is if I have a user that has written data to the database, then that database, if I just go with my setting here, is going to be overwritten with the original database from the application itself. So that is something that you need to have to take into account but the key points here is that ClickOnce is just a great way of deploying compact databases, could also be used to deploy SQL Server and Express Edition. And hopefully, you have an idea about now how to get started at least with the ClickOnce framework.
Types of Local Data
Now when it comes to working with the data formation already, when you deploy this, you have to take into account what type of data we’re working with. So it’s just read only data, meaning that it’s all static, the data never changes. We might deal with things such as product catalogue, for example., and the end users can’t update the product catalogue. It can only be updated centrally, which means that there’s a new version of the product catalogue database. We can deploy that in the next version of our application. We don’t have to care about potentially overriding changes that the end user has made.
Well, if there are user data, so they have their own data, they make inserts and updates and deletes to that. Then we need to come up with a scenario where we need to come up with a process that allows us to manage all of that. And that’s where synchronization is going to be important; to make sure that users synchronize up to a centralized repository. And we might not include the database when we deploy applications after that initial release--meaning that we deployed the application, we then go ahead and deploy that with the initial database. And then all the updates we deploy without the database, so that we can persist the users changes to that database, that they are working with locally. And then we simply just synchronize in that database when they connect to a centralized depository.
Again, that’s something that we’ll talk about in the third session. The majority of the third session is going to be spent on dealing with programming, development and synchronization is going to be one of the key points that the work is there.
Securing SQL Server CE 3.5
Now securing SQL Server is obviously going to be important because since this is living on individual devices, if that laptop or handheld device and what not. If they get stolen, then we need to make sure that we are protecting the database. Now, I’ve only showed you that you can protect the database with passwords. There are a couple different options to how you’re going to deal with the encryption, the platform default, engine default. If you’re working mobile phones, you know, whatever the encryption model is there. You can also use the file system encryption EFS, if you’re working on a Windows desktop. And the two screenshots right here, up here, you see what I showed you before what will be displayed when you create a new mobile database. You can supply a password and you can also pick the encryption mode.
This down here is where we see when we actually right click and look at the properties or the attributes of a folder and then we can say on this folder I want to enable encryption and everything within that folder is then going to be encrypted as well.
Now if we use passwords, the important thing you have to remember is that the password is only going to be secured if it’s stored in an encrypted state. So again, if I have a password but it’s stored as clear text in the registry, or in a connection string in an XML file or whatever, then things aren’t really secure, because it’s not going take a very skilled hacker to figure out how to get to the password. So, if we have a data access laid, that’s where we see represented over here where we have a function called create database. What that means is that we can actually create the database from the application itself, using code. That’s something that I’m going to show you when we do the development session. I have an example there where I create the database, not when I initially deploy the application or use the application, but the first time somebody tries to sync from the centralized depository, then we’re going to create the database.
So, we can create the database programmatically as well. And then we have another call here that’s called “get data from database” and we have a clear password so the database itself is encrypted but the password is just sent in clear text back and forth. If we store it in the registry, then it’s “hidden” but it might still be in clear text. Again, it’s not going to be terribly difficult to figure out how to retrieve that password from the registry. So you can see that we have the same database calls here from the data access layer but when we say, create database or get database or get data from database we are just retrieving the password from the registry before we actually submit that to the database itself.
So while we can do is we can encrypt the database. So we can use the CryptoServiceProvider so you could see that now we have another class called password manager, that is going to deal with getting, retrieving the encrypted database and that password is then going to be sent, passed along to my data access layer and then passed along to the database itself. Since we’re working with everything on the local machine, we don’t have to be as aware of encrypting the data when it goes across “over the wire’ because there’s not wire we’re going over. Everything is done locally but we want to make sure that if somebody gets to the application itself, they can’t get to the password without having the proper keys, for example. So we have an asymmetric key pair and we need to make sure that they cannot get to the private key that is necessary in order for them to unlock the data to get to the information itself.
So, if we look at different layers of security and this is working with a mobile device, we can have a pin to the mobile device itself. We can encrypt a password, we can encrypt the database and then we can encrypt the entire file system. Again, encrypting file system, it’s on desktop only so we can’t do that on mobile devices. But here you can see it’s sort of a pyramid of least secure, more secure, even more secure and the most secure option, to have all of these options being implemented on your database solutions.
SQL Server CE maintenance
When it comes to maintenance, just general database maintenance, I mentioned earlier that maintaining your SQL Server Express Editions is fairly similar to managing any other database, any other multi-user database meaning that we have the option to go in and deal with file groups, we can deal with backup options and recovery models and what not and when we deal with compact editions or SQL Service CE, we don’t have nearly as many options. Really, the only options we have is how we want to deal with shrink and repair. Snd this is something that can be quite useful to be aware of and this is also something that we can manage, not only from Management Studio. So in other words, if we just work with SQL Server Compact, the SQL Server CE, we don’t have to install SQL Server Management Studio to manage it. We can do it directly from within Visual Studio. Now, obviously if I’m just a DBA and I’m not a developer, I don’t write code, I might not have Visual Studio. Well, then you can use your traditional SQL Server Management Tools to manage SQL Service CE as well. I’m going to show you that too here in just a little bit.
So, when it comes to the maintenance, as I mentioned already, what we can do is, we can do a repair, we can compact database, we can change the password, we can verify the database, we can do that using code but you can also do that using just property pages. When it comes to backing up and restoring and dropping the database, that’s all just file copies, just another file that you copy somewhere and that’s the backup. So we don’t get that fully managed process of backing up and setting up a schedule and what not as we would get if we are using a multi-user database express and standard enterprise and what not. Now, we just have one file, there’s no transaction file. We copy that somewhere and that is going to be the backup.
Demo
So let’s go ahead and just quickly look at some of these management options as well. So, if you look at the database here, this is in Visual Studio. Here is the MyDB.SDF that we were working with. Then I can go in, we can look at properties from here and notice there that when I click on properties, I’m just going to see the connection string, then it’s going to say that they state is closed. So, if I go ahead and I actually open up, I expand here so I actually open up a connection to the database, this might be hard to see but it’s a little read x when it’s not open. That x goes away when I do have a connection, when we go in and now when I look at database properties, notice there that this is where we see that properties window that I showed you before, we can see the location as well.
Notice that now we’re looking at the location and it is going to be in the application folder as well. So I don’t know if you remember this but when I initially created it, it was in the My Documents folder, now we have it in the location of where the application is being stored. And here we have the shrink repair so I can say that every time I close a connection, I’m going to do a full database compaction. And if I don’t specify a new destination, then it’s going to be an in place, meaning that it’s going to be done on the database itself. I can also create a new database compact and create a new database that is then going to be used. I can also do a repair, I can also recover corrupted rows. And again, this happens when I close the connection to the database. If I do this using code and then I also have the ability here to do it from the dialogue box. If I’m not a developer, I don’t have Management Studio, well, I can just connect to it from here, from Management Studio.
So I can go ahead here and I can say that I want to browse for the database so I believe we will see it on my C drive and then we had Windows application, Windows application 2 and then right there is the database. We can connect to that. Now, I’m connecting to Management Studio. Obviously, you can see we have the same information here, I can browse my data, I do select statements from here. Notice when we go up here and do queries that we have SQL Server Compact Queries. So that’s what I’m executing here and then we can say select star from your simple - one here – it should be empty but I just remembered that I did an insert in the data into that database. And if I right click here, notice here that I’m not right clicking on an instance, I’m right clicking on the database itself.
When I deal with traditional multi-user databases, there’s the instance, I have all my databases, here I connect directly to the database, we can go down to properties and you can see that we have those same exact options as I saw in Visual Studio as well.
Now, the last thing here is we can also set the password so if I don’t have a password, I want change it and whatnot, then I can do it here from the dialogue box. Now remember here, I mentioned that everything that we do here can also be done programmatically.
So, what I’ve shown you here is a little bit about deployment, working with ClickOnce. I showed you how to use SQL Service CE, we could also do ClickOnce with the Express Edition; how we can deal with prerequisites and make sure that those are installed before the application is installed; that we deal with versioning properly so that the application is aware of the fact that there is a new version out there which is just a great feature of the ClickOnce deployment framework. And then we’re also looked a little bit at some of the management options that we have which again, aren’t nearly as many as compared to working with a multi-user database.
One quick thing that I want to mention before we part ways is the Front Runner for Innovate On Microsoft SQL Server 2008, which is a great program if you’re an ISV and you have an application that is going to be done by April 3. You can submit that application to be part of this program and by doing so, you get lots of free technical advice, you get a testing voucher to go through the testing process for your application and you get to take part of some co-marketing activities with Microsoft.
So if you’re interested in this, please use the email alias that you see here, usisvte@microsoft.com. Send them your information, contact name, company name, phone number, solution name, etc. and they will get in touch with you and you can also ask more question about this particular program.
As always ISVInnovation.com is the best place to go for lots of information about SQL Server and other Microsoft technologies so with that I would like to say thank you very much.
[End of Audio]
