Video Transcript
Welcome everyone to this series where we will be talking about SQL Server 2008 Compact and Express Editions. This is the first part in a series of three. So we have the overview part and then session two we’ll talk about deployment and manageability, and then in session three we’re going to talk about application development.
Now one thing that I want to point to you is, and when we say SQL Server 2008 Compact Edition, that is the SQL Server CE 3.5, latest version with Service Pack 1. That can be a little bit confusing sometimes when you go online and you read up on documentation and whatnot. Sometimes it’s referred to as a SQL Server CE 3.5. That is the official version but lately, especially if you go into the SQL Server 2008 websites that Microsoft is hosting, then you’ll see that they just call it the Server 2008 Compact Edition. So just to clarify things when I talk about the Compact Edition, it’s the 3.5 version.
What is SQL Server 2008 Express
What we’re going to start off with here is just a little bit about SQL Server 2008 and Express and then later on we get into the Compact Edition. So, first of all, if you haven’t worked with this before, this is SQL Server meaning that we’re using the SQL Server engine. It’s not a sort of downsized version of SQL Server, it has some limitations to it, meaning there’s some limitations to database size, there’s some limitation to the amount of memory that can be used. But other than that, it is SQL Server and it is free and it’s really free, meaning that there’s not something that you download and can use for 30 days. Or there’s not, you know, a specific version or features that you have pay for. When you download SQL Server Express, it is free for you to use and also to distribute.
Now there are three different versions of 2008 Express and I’m going to go in and explain a little bit later on. So when we say here that it’s limited but not governed; what that means is that what I just mentioned the fact that there is a limitation to the database size, there is a limitation to the amount of memory that can be used as well.
Designed to replace the jet engine, it supports reporting services. So you’re going to see a couple of examples here. Later on I’m going to show you what you can do with SQL Express and with reporting services.
So, it is a really nice offer, especially for companies that might not have hundreds or thousands of users. It might be that we have, you know, 10 users. That’s our company, and we don’t want to go out and spend, you know, thousands of dollars on software when we have one database where we have let’s say, you know, 15 tables, we have a couple thousand rows of data. We’re not, you know, updating this, you know, a thousand times a second. It’s something that gets updated once a week, once a day. And being able to use SQL Server Express for those types of situations is ideal, because as I mentioned already, it is a full blown SQL Server platform.
Obviously it’s not going to support all the same features as the Enterprise edition is doing and I’m going to get into a little bit more about that later on but it has lots of functionality as mentioned already. As I mentioned already, the fact that we can use reporting services, it has great tools that allows you to manage your database. So we do get SQL Server Management Studio, just like we would do with the Standard or Enterprise Edition. You’re going to see that it’s called SQL Server Management Studio Basic and I’ll show you what that tool looks like here in just a little bit towards the end of this session when we get into some demonstrations.
Now, as I did mention, there are three versions of the Express Edition. So we have the one that is called just the Express Edition, we have the Express Edition with tools and then we have Express with Advanced Services. So the way these work is that each version just adds on more functionality so the most basic one we have is SQL Server Express. You can see here the features that are available. So, for example, all of them is going to have import/export wizards to import and export data and the Express version with tools and the Advanced Services Edition is going to have Management Studio Basic.
You can also see that the Advanced Server Edition is going to have integrated full text search. We have, you know, MERGEfunctionality in all three editions. You do have support for the new data types that were introduced with SQL Server. So you can see, in fact, that we have file stream, the file stream data type, we have support for new date and time data types. We have support for geospatial data types, spatial standards, etc., etc. So a lot of the functionality, the new functionality, the new data types and some new T-SQL functionality that was introduced for 2008, that you might have seen people use with standard or the Enterprise Edition, is available with the SQL Express version as well.
When it comes to replication, you’re going to see that you’re not going to have the same replication functionality as all the options that you would have with the Enterprise version where you do have the ability to work with change tracking. Which is great if you want to build solutions, that need to synchronize with some sort of device. So if you have a Compact Edition installed, e.g.,. on a laptop or handheld device, and you want to set up synchronization so that we can use the Express Edition as the centralized repository that everybody synchronizes to. Then the ability to use change tracking is great because if you haven’t used it before, it’s pretty much what it sounds like; the ability to track changes over time.
We also have the ability to work with sync services and that is a great framework and it’s something that I’m going to talk about in the development session, which is the third session. We’ll discuss how we can use sync services and the sync services framework to create a robust infrastructure that allows us to very easily keep track of what should and should not be changed and how to do we merge data, how do we synchronize data when we have lots of disconnected devices.
When it comes to reporting services, as mentioned, if you get the Advanced Services Edition, then it comes with reporting services support and we do get lots of the new cool stuff that was introduced with 2008, e.g., the ability to work with reporting services without being dependent on IS, the ability to use some of these new charting and gage tools that are available. You get business intelligence, developer studio to allow you to build your reporting services reports.
So really, the thing here is when you choose which version you want to work with, it’s really a matter of well, what functionality am I interested in? Do I just care about the bare bones functionality? Do I want to have a, you know, a management interface? I would assume that most of us enjoy having a GUI interface that we can use to manage our databases. Do I care about full text search? Do I care about reporting services? If the answer to that is yes, well, the addition that you should download is the Advanced Services Edition.
Couple of things about the prerequisites, something that I might myself became painfully aware of when I did my install here and on my demo machine. It took a little bit longer than I anticipated just because--I did know that that we needed the 3.5 Framework, so that has to be installed. But you also need to have Windows installed, the 4.5 or above. And if you install Advanced Services Edition, you also need to have the Windows PowerShell installed. And the reason for that is because PowerShell is so ingrained, so integrated with SQL Server. If you worked with SQL Server 2008 before, you know that there’s lots of features available where we can right click within Management Studio and kick off a PowerShell session and then use PowerShell to go in and modify or manage our SQL Server objects. So, that’s just a quick little glimpse on the differences between these three different versions that we get of SQL Express 2008.
Another thing that I wanted to show you here, I just took a screen shot when I went through an installation, the installation itself, the interface that is being used is the same one as you would see if you install Enterprise Edition or Standard Edition or WorkGroup and whatnot. Obviously here, when we get to the features selection, it’s going to be different compared to other licensed versions of SQL Server. So this is a screen shot going through the installation of the Advanced Services Edition. So you can see that this is what you can choose from so you don’t have the ability to install things like Analysis Services or Integration Services and whatnot. But full text, as we mentioned already, some functionality and when it comes to SQL Server replication.
You can be a subscriber but you can’t be a publisher. So you can subscribe to merger application, sorry, not merger application but T-SQL replication, reporting services, as I mentioned already. And then we do get bids which obviously is great since we need to, if we install our reporting services, you want to develop reporting services report where we get an environment for that. We get the management tools, we look at that, we get an SDK and you also get the sync framework, as I mentioned briefly that you have the option to install.
Other than that, the install process itself is, you know, pretty much the same as installing any of the other versions of SQL Server, you need to provision account, you need to pick data director set up, identities for your accounts and one thing that I want to mention here is that you don’t get a SQL Server agent so if you’re just thinking about, you know, functionalities that’s supported by the SQL Server Agent, that is not going to be available in SQL Server Express.
So, as I mentioned already, it is, you know, SQL Server. It is this SQL Server engine that we get. There is no limitations to the T-SQL that can be used. Again, if there is anything specifically supported in just the Enterprise Edition and obviously we can’t use that in Express but it’s not the same as working with the Compact Edition, for example, where we get a subset of the T-SQL that is supported. We get all the T-SQL, we can write stored procedures, functions, views. It has support for managed codes. If you want to write managed stored procedures or functions, user defined types and all of that is supported. Obviously you can go in and configure security, the ability to deal with backups and restores where we have replication, there is no merged replication. So we do have some other transact SQL replication types but no merge replication. And also, you can only be, as I mentioned, you can only be a subscriber and subscribe to existing replications that have been set up, you can’t be a publisher.
Why use SQL Server Express
So, why do we care about SQL Server Express. I mentioned a couple of options, reasons already. The fact that, you know, there’s plenty of organizations out there that don’t have hundreds or thousands of employees that don’t need a database that is going to scale to be terabytes up in terabytes. They just want a secure, stable data repository that has lots of availability when it comes to developing custom applications, that want to work with .NET. They might also have an application where they want to distribute data repository and you can certainly use SQL Express for that. That’s one of the reasons why we have this most bare bones SQL Server Express version, the fact that we can take that and all you need are repository. We can scale off or remove all of these other features, reporting services and what not, that your particular application, that you want to distribute. Well, you don’t need them, so let’s just go ahead and give you the bare bones minimum, you know, IT friendly because the way you manage this is like managing any other types of SQL Server Express.
Obviously, if you do have a mixed environment, so you might have Enterprise installations and standard installations and the web edition installations and what not, if you have that, you can manage SQL Server Express using the same management tools. You don’t have to use this basic version of SQL Server Management Studio, you can use the full blown version of Management Studio and manage as you’re probably aware of already, and manage everything from Enterprise to Standard, all the way down to the Compact Edition of SQL Server.
It is a very flexible solution meaning that we can go from scaling to, you know, hundreds of users. Again, as long as we don’t have any issues with the limitations, in terms of the size of the database and also in terms of how much memory that can be utilized. Remember that the limitation there is one gig for memory and four gig size for the database itself.
You can also use a user instance implementation which is something that some of you might have done, if you worked with, you know, creating WinForms or web based applications. Essentially what that means is that you get a user specific instance. The name sort of pretty much gave that away already. What happens then is that you copy an instance of the database into your profile folder. You are going to work with that. You might have seen these connection strings that sometimes get created if you work with applications in Visual Studio where you just point to a data directory and there is property called User Instance. So we can go from User Instance versions where we connect directly, essentially attached directly to the MDF file to a shared, you know the way that we traditionally use a database where it’s shared, amongst 1, 2,10 ,100 users.
Obviously you can use this for not only internal applications. If you have a public website, you can most certainly use the SQL Express edition for your public websites as well.
If you want to compare this to the Jet engine, again, this is meant to be a replacement for the Jet engine, we have now a real database server. It’s not going to be the local file share SQL engine type of deal that we have with the Jet engine, you know; no I/O, no physical I/O done over the LAN or WAN. Security; it integrates with either SQL security, you can integrate it with active directory, if you want to, you get transaction logs, lie backups. You know it’s just another database, we don’t need to deal with compression or compacting the database.
And you can also start off with Express Edition and then, if you have the need at some point to go out and purchase SQL Service. You might start out with this as a departmental solution and the solution grows. So you get more users and at some point, you might realize that well, we really need Standard Edition now, we need the Enterprise Edition because the database is growing too large, we have too many users, we need more memory and what not. You can obviously upsize, or whatever you want to call that process, to Standard or Enterprise or some other versions of SQL Server, even if you started out using SQL Server Express.
Now there is a, if we look at SQL Server, not talking about Standard or Enterprise, but the lowest “version” that you have to pay for. What is the difference between Express and that version? Well, as I mentioned, we don’t get Enterprise features support. So there’s no real high availability support or OLAP support. We use only one CPU. You do get full text indexing. So that’s, what we see right here is actually I should remove that, I missed that out. I apologize for that but do get full text indexing. Only uses one CPU as well. So in addition to what I just mentioned, the fact that we can only have one gigabyte of memory and the database can only be four gigabytes, you can only use one CPU as well. So, even if you have, you know, four CPUs in your machine, if you have, you know, 16 gigabytes of memory, it’s going to limit itself to whatever thresholds that we listed on this slide, right here.
Couple of other things too, in terms of when would I care about SQL Express. One thing they might need to be aware is the fact that you can only have 50 instances per system. So 50 individual instances of SQL Server Express. The other topics that we see listed here, I’ve already touched upon the fact that you know, great for smaller web sites, a local data store so a local data store on your machine, so, if you have laptops especially. It’s going to be too big to put on hand held devices, that’s why we have the Compact Edition. But if you need full blown SQL support and you have lots of, you know, laptop users that might be disconnected and that’s the reason why they need a local store, then this is just a great solution or option for that. And then, as I mentioned, later on we’re going to talk about, in one of the other sessions, we’ll talk about how we can use sync services to synchronize what’s being stored on a local desktop, for example, and then synchronized that up to a centralized repository.
SQL Server Express Tools
Some of the tools that we’re going to look here in just a bit, is Management Studio, as I mentioned already, you also get SQL Server configuration manager, you get the command line SQL CMDs available. You also get PowerShell integration. So if you use the Express Edition with Tools or the Advanced Services Edition, then you have the ability to work with the PowerShell, as well, which is another really, really great feature.
New SQL Server Express 2008 Features
I want to mention, before we move into talking a little bit about the Compact Edition and then do some demos, but before we do that, I just want to mention, some of the new features of SQL Server Express. Obviously this is compared to older versions, so the SQL 2005 Express version. You do get a 64-bit version. With SQL Express 2005, you just had a 32-bit, now we get a 32- bit and a 64-bit version. Support for the new data types, I mentioned that already but that’s something that we definitely want to point out again; the ability to work with geodetic, hierarchy ID, spatial data types, file stream and whatnot. All of that is supported in the Express Edition; the integrated full-text surge; changed tracking. Again, changed tracking is something that we’re going to look at in more detail when we talk about sync services.
Couple of other things, supports LINQ, supports the ADO.NET Entity Framework, the input, export wizard that I mentioned. And it allows you do just that; import, export data from other data sources. We do get reporting services and the way the reporting services work in other versions; the fact that it’s not dependent on IS, the fact that we do get new controls that allows us to do better visualizations of data. So the ability to work with new gauge control, some of the new charting controls. All of that is available in the Express version as well.
What is SQL Server 2008 Compact Edition?
So, that being said, let’s move into and talk just a little bit about SQL Server Compact Edition. Again, the Compact Edition, it’s SQL Server Compact 3.5. So when we read about 3.5, it’s the same as the Compact 2008 Edition. Hopefully that makes sense, I might have made that even more confusing. But that’s how the versioning goes and again, it can be a little confusing because it’s not consistent across all web sites, how people refer to this, but we’re talking about the same thing.
Now, we’re saying here that it’s not really SQL Server, what that means is that we get a subset of functionality; like we don’t get full blow T-SQL support. Obviously there’s going to be a different set of APIs that we use in terms of working with ADO.NET. And the reason for this is to that we can get a database that has a very, very small foot print. So the goal here is to have a database that we can deploy on very small devices; hand held devices, cell phones, Zune for example, if you use that, it’s using SQL Server Compact Edition, just to give you one example.
Now, couple of other things that are really nice is the fact that you don’t have to be an administrator to install this. So, if you’re using ClickOnce, we’re going to talk a little bit about more about that in the admin and deployment section, but if you have an application that you deploy using ClickOnce, then this is a great database platform because it can be deployed with ClickOnce. You don’t have to be an administrator to install the Compact Edition. It runs in the same process as your application, very small footprint, as I mentioned, supporter of multiple platforms, including cell phones.
Some of the limitations here are that we don’t get stored procedures, we don’t have the ability to work with XQuery, for example. If you want to work with XML and query there, we don’t get support for file stream, just to mention a few things that you don’t get. We have a subset of data types, the T-SQL that we use as well. We don’t get all that same functionality like MERGE and up search and what not, supported in the Compact Edition.
Typical SQL CE Architecture
So, here we have an example of what a typical SQL Compact Edition architecture would look like. Well, we have an application that runs locally that does whatever, tracking sales information or customer information, you know, we build a CRM System and what not. And we can have, you know, data access layers, logic, business logic layers available running on whatever this device is. If it’s a laptop, it’s a handheld device, if it’s a cell phone, we store all that data in our SQL Server Compact database.
And then we need to have some type of synchronization framework. So either if we roll our own synchronization framework, or if we go about using the sync framework or if we use RDA. There’s a variety of different ways that we can synchronize. Then we need to set up, depending on the framework, we might have to set up a web service proxy that allows us to communicate with this backend server database. What we see over here is going to be something like Standard or Enterprise or even SQL Server Express, a multiuser database. But we can gather all of the information from one or more users and then, we need to go through the process of figuring out well, how do we merge all of this data; how do we deal with conflicting updates or conflicting changes to the database itself. And again, in the development session, we’ll talk more about that in detail.
Now, if we want to compare Compact and Express Edition, I have just a quick little slide here about that. Obviously, with the Express Edition, we get more functionality, in terms of what we can do with security and programmability and what not.
Demo
So, that being said, let’s go ahead and just do a quick little demo where we get to take a look at these different environments an d get a better idea of what they look like. So, let’s look at SQL Server Express and Compact Edition. I got both of them installed here, in this particular case, it’s on the Windows Server 2003. But this is something that you can install on XP or Vista or Server 2008. So it doesn’t have to be installed on a server platform.
If you look at the Tools here first, so if you go to SQL Server, you can see that it looks, at least, at first, fairly similar to any other SQL installation. However, you can notice here that we don’t have things like the SQL profile, for example, that tool does not ship with SQL Server Express. But you can see here that we do get, we do have the ability here to work with importing and exporting data. We also get the ability to use the data profile view, so if we have a profile that has been created, we have created a data profile and that is something that you would have to use SSIS for.
Again, remember that we don’t get Integration Services with Express so we can’t create a data profile but we can view one. We could also execute existing packages, we get some configuration tools to configure SQL Server Reporting Services and if we go ahead and open this up – I’m just going to have a quick look here at the configuration manager, you can see that these are the services that we have installed, that is running right now. So, we do get the reporting services, we do get the full text filter, we obviously get SQL Server as well. You can see that we get the usual suspects here when it comes to the network protocols that we can use as well.
One thing here that is interesting if you’d like is the fact that the agent is included here. You can’t actually use the SQL Server agent. I’m not quite sure why it’s listed here, why they would even bother putting it here. I just want to show you that if I go into properties, if I wanted to change that from disable, which it is right now. And if I wanted to go in and change that to manual or even automatic, I’m going to get a message that says that that type of request is not supported.
If we continue on and we look at SQL Server Management Studio, you can see that that’s what we have open right now and from here I can go ahead and work with my Express Edition that we have. I’ve also attached already to a Compact version or a Compact Edition database right here, you can see that we have good old Northwind that we’re working with. So, if I go in on the connect, you can see that those are the two types of database engines that I can work with. So, I can’t connect to let’s say, Analysis Services or Reporting Services or Integration Services, something that I can do if I were to use, let’s say, Management Studio that ships with Standard or with the Express Edition.
Now what I have here if we start off with Express, I have attached a database, Adventure Works. And this is the Adventure Works 2008 sample database, the same one that you would work with on, let’s say the Enterprise Edition. So all the functionality that we have in Adventure Works, it works on Express. Just to show you a few things here; I can go in, we can work with assemblies. So here you can see like data types that we have if we have, you know, CLR data types or we have the hierarchy ID, for example, which is new that was introduced with 2008, you can see that we have that. And obviously you can deploy and work with drone customer assemblies if you want to. You can work with the stored procedures, it’s something that we mentioned earlier on. So stored procedures and functions and views, XML, XML schema collections, all of that is supported. The file stream data type, it’s supported as well. So there is lots of really, really useful functionality here.
If we go ahead and we look down a little bit, you can see with security, we can go in and I could go in here and I could create new credentials. So that is something that is supported. We have server roles, just like with have in other versions of SQL Server, you can see that we have our logins as well. If we look at server objects, I can go in here and work with linked servers if I want to. We could work with instance level triggers as well.
When it comes to replication, notice here that I don’t have publications. I mentioned this a few times already, we only have the ability to subscribe. And if we look under management, we have the ability to go in and obviously look at logs and what not. Notice here again that we don’t get things like the ability to work with the resource governor, that is something that is an Enterprise Edition feature.
We do have the ability to work with policies. The only thing here that I want to point out and we’re going to talk more about policies when we get into management which is the next session. But I just want to point here, real quick that with the policies here, I cannot enable or schedule these policies to run automatically. All of these policies have to be run manually, meaning that I can work with policies. Here are the sample polices to ship with any addition of SQL Server and I can right click and I can evaluate the policies from here, but I cannot go ahead and schedule these policies to run. You can see here, if I open this up and look at the properties here and we say, I want to do this on schedule, you can see that we can’t do that because we don’t have a SQL Server agent that is required for us to do this cause it’s going to be scheduled as a job. So that is something that, you know, you want to be aware of. Obviously, it’s going to tell you lots of times before it actually goes through and complete that particular operation, but you can only run your policies manually.
However, what you can do is you can use the PowerShell if you want to. You could schedule just using Windows Tasks, the PowerShell to execute and then the PowerShell would execute policies. So that’s one way to get around the fact that we can’t enable these policies directly from within here. You can see here as well, if I try to enable Policy Management, it’s going to tell me that you can’t do that in this particular edition of SQL Server. Again, it doesn’t mean the policies don’t work, it just means that we have to execute them manually.
There’s just a little bit about SQL and let’s go ahead and just look at some data here. Obviously we have views available as well. So I could go in here and we can go in and let’s look at a – one of these views, just to open it up to show you that we’ll get some data back as well. And it looks like it, you know, typically, normally would do inside a Management Studio.
From within here, we can go in and you can look at execution plan as well. So you can see that we have both estimated execution plan and the actual execution plan. All of that is available in the Express version. So that’s obviously a very, very neat feature, you know, there are various different options here to get results into text, get into external file, if you want to. So lots of the typical functionality that you were to expect in Management Studio is available even in this basic version.
If you look real quick here at Compact Edition which I have right here, the Compact Edition, what I’ve done here is I’ve connected to a file directly. If you’ve never worked with Compact Edition before then that’s how it works. And again, it’s the 3.5 version that we’re working with here. You can see it as right off the bat, we notice that there’s lots of functionality that we don’t have like, you see that we don’t have things like management, for example. We don’t have the ability to go in and look at stored procedures cause stored procedures aren’t supported. You do views, got a couple of tables here. If I want to go ahead and create a new table.
And we’ll look at things like data types, you can see that the data types that are supported. We don’t get everything that we would see in other versions like XML for example. We don’t see the native XML version in here. If we go ahead and look under programability, the only thing we see here are types, so these are, you know, our SQL types that are built in. Again, we don’t see things like user defined functions or custom user defined types or stored procedures.When it comes to replication, again, we can only act as a subscriber. We can’t act as a publisher.
But that hopefully, gives you just a quick, brief little introduction overview of both Express and Compact Edition. And again, this is just the first session. So in the other two sessions, we’ll talk more about management and deployment and also developing applications.
So, again, this is the first session that we’re coming to an end of right now. And I just want to mention one thing here real quick and that is the Front Runner for Innovate On Microsoft SQL Server 2008 is a great program. If you have an application or an ISV and you have an application that you think that you can get deployed by or finished up by April 3, then by participating in this program, which is free, gives you lots of good stuff. You get some free support; you get some testing vouchers to test your application to verify it, that it runs on 2008. There’s lots of other things like co-marketing activities and what not. So you have an email alias here usisvte@microsoft.com where you can email and get more information about this particular program.
And as always, isvinnovation.com is the number one place to go to get lots of information about SQL Server and other technologies as well. So with that, I would like to say thank you very much.
[End of Audio]
