|
With the release of SQL Server Everywhere Edition looming, Scott sits down with Mark Jewett and Steve Lasker to find out about Microsoft's new database engine.
November 01, 2006
URL:http://www.drdobbs.com/database/sql-server-everywhere-just-another-datab/193402926
Scott is a consultant specializing in convergence of current and emerging technologies. Scott can be contacted at [email protected].
With the release of SQL Server Everywhere Edition looming on the horizon, Scott Swigart was able to sit down with Mark Jewett, Senior Product Manager of the SQL Server Team, and Steve Lasker, Program Manager and Technical Lead, to find out what's what with the new database engine.
DDJ: Microsoft is coming out with SQL Server Everywhere Edition. What is it and why do we need yet another database?
MJ: SQL Server Everywhere Edition is, very simply, a database designed for a client environment, for client applications. First, it's only about 1.5 MB and when you compare that to other SQL Server products, it's a very small footprint, which also makes it easy to download. Second, it runs in-process. The end user of the application doesn't even need to be aware that there's a database. It just operates seamlessly within that application. Third, everything developers know about SQL Server today is applicable knowledge; in other words, the T-SQL and objects that you are familiar with if you use SQL Server today. You have many of those same things with SQL Server Everywhere Edition.
SL: When we started to go public with what we're doing with SQL Server Everywhere Edition, people said, "Oh, great, that's exactly what I wanted," and then often the very next question was, "How do I synchronize with the data store?" Everyone wants one big database that stores everything, but that never happens. Most companies can't even consolidate where they have their list of phone numbers, much less consolidate all the data. Once you accept that you're going to have many databases within an organization, the question is, how do you get those disparate data islands to communicate? That goes back to the synchronization strategies we're working on with SQL Server Everywhere Edition.
DDJ: There are some applications that are never going to synchronize with SQL Server, but there are a lot of applications that want to work offline and sync, or just want to cache local data for performance. What are some of the SQL Server Everywhere Edition features that support those scenarios?
SL: When using SQL Server Everywhere Edition, it's focused on having the proper subset of the SQL Server product features. From day one, it's used a subset of the T-SQL language. The technologies that developers may need to deal with are growing at an exponential rate. The number of technologies that we deal with inside of Microsoft is growing exponentially. Developers can't keep up with 15 ways to do the same task. But, if you can use the same skills when you start with the client, and move to the server, that helps keep developers productive. That's where SQL Server Everywhere Edition takes off. It uses the T-SQL syntax and the ADO.NET programming model. It's a subset of the SQL Server feature set.
Now, when data gets spread out in the field, how do you roll it up in the central store? If you have data in the central store, how do you push it out to the field? How do we make that easy? One part of the answer is that SQL Server Everywhere Edition has a subset of the SQL Server data types. So we don't have the data conversion issues that you would have with Jet or other local database options. For example, we have nchar and nvarchar. SQL Server has data types like XML, which SQL Server Everywhere Edition doesn't yet support, and in that case, we convert it to NTEXT. We don't lose any of the data, we just don't have the XQuery functionality in SQL Server Everywhere Edition that you have with SQL Server 2005.
Once we get past what we're doing with our 3.1 release, we'll build on that in our 3.5 release with a new set of synchronization components. The synchronization components will also have a better together story with SQL Server, even though they will let you sync with other back ends. You'll be able to leverage your existing ADO.NET knowledge, and extend that with a component model that does synchronization. We're referring to that as our "synchronization framework." If you think about ADO.NET DataAdapters, we'll be shipping SyncAdapters which let you compose together a synchronization infrastructure between a server and the client.
DDJ: Let's say someone is working on something like Microsoft Money, which doesn't need to sync to any central server. You don't need to do anything more than back it up. What makes SQL Server Everywhere Edition a good engine to pick for strictly the local data storage scenario?
SL: There are a couple great questions there to think about. The first question is, "What functionality do I need?" If all I care about is local storage and I don't need to sync something like a product catalog, then you might look at your engine and ask, "Is it small? Can I put it in my application?" In many cases it's the application that's central, not the database that you've chosen for storage. You might also ask, "Does it impact the installation or runtime experience of my overall app?" SQL Server Everywhere Edition being small, lightweight, and in proc, makes it a great fit for that. Once you get beyond that, you have to look at its core features. For example, "Does the engine have a query processor?" Absolutely. "Will it be able to handle additional loads?" When you only had 10 products, you might have been able to use XML or a text file, but once you get to hundreds or thousands of products, you may run into issues that you never had before. That's where having a query processor helps in two ways. First, it lets you get a subset of the data in an efficient way. Second, how do you bring just the information that you're querying into memory? With XML, you can query an XML document for certain elements, but often you've had to pull the whole XML file into memory to do that query.
Transactions also become important. Say I want to make an update, do a delete, or do an insert. If something fails because I lost power, dropped the device, or violated a business rule, how do I ensure that things are committed in an all-or-nothing boundary? In many places, people say that if you just give them a query processor and transactions, that's all they need.
There are a couple products that currently meet that bar. Then, you need to look down the road two or three years, and look at where Microsoft is going to be making future investments. As new scenarios become important, for example XML as a database data type, or new hardware architectures evolve, you have to ask if the database engine is going to continue to be enhanced. Will the engine run natively on 64 bit, for example? In the long run you have to look at where Microsoft is strategically investing. Microsoft is saying, very publicly, that SQL Server Everywhere Edition is a very important strategic investment. From a tooling perspective, Visual Studio will be doing a lot of work to make sure that SQL Server Everywhere Edition is a primary focus in our development experience.
DDJ: You've mentioned that you'll be shipping Version 3.1 before Visual Studio "Orcas", and Version 3.5 with Orcas. You've also mentioned that, even though it's called SQL Server Everywhere Edition, there really is no "Server" to install, it's just several DLLs. So I'm guessing that in the future, one application could be running against Version 3.1, and you could drop a new application on the same machine that's using Version SQL Server Everywhere Edition 3.5, and both database engines can just run side-by-side. Is that correct?
SL: That's a fundamental principal of what we're trying to do here. If I have App 1 installed and working, and I install App 2, I want to make sure that App 1 doesn't break. We will absolutely support side-by-side installations of SQL Server Everywhere Edition. There are pros and cons to that, but for most of our customers, they see side-by-side as a pro. If App 1 is running against Version 3.1, and Version 3.5 comes out, App 1 keeps running against Version 3.1 until the development team for App 1 decides that they want to move it to 3.5.
DDJ: Talk a bit about servicing the engine itself. If the engine has a bug or security vulnerability, how does it get patched?
SL: With 3.1 we'll support two deployment models. The first is, if you use the MSI to do a traditional install, SQL Server Everywhere Edition gets installed once per machine. In this case SQL Server Everywhere Edition gets direct servicing from Microsoft Update, which is a servicing model that users can opt into. The challenge with that model is that it requires administrative rights to get SQL Server Everywhere Edition on the machine. The SQL Server Everywhere Edition product is made up of native and managed components. The native components don't require administrative rights to install. They can be used from the Documents and Settings folder. In this model, where SQL Server Everywhere Edition is deployed simply as DLLs bundled with your application, Windows Update will not update SQL Server Everywhere Edition. The application vendor will have to service those DLLs with their application. Many application vendors have often told us they don't want Microsoft to service those bits. They want to know when a fix is available, test their app with that fix, then deploy a new version of their app with the SQL Server Everywhere Edition fix.
What we've enabled in the 3.1 release is you can go to a Windows Forms project, add a reference to SQL Server Everywhere Edition, copy the SQL Server Everywhere Edition DLLs into the application project, and have ClickOnce deployment deploy everything for you. When you deploy an application this way, you'll see your program in Add/Remove Programs, but you won't see SQL Server Everywhere Edition.
One of the interesting security aspects of that, which is an artifact of how the .NET Framework works, is that when the assembly loader loads an assembly, it always looks in the Global Assembly Cache (GAC) first. If it doesn't find that assembly in the GAC, it will then look in the application directory. If companies get into a problem where there is a critical servicing issue with SQL Server Everywhere Edition and they're not sure which machines have SQL Server Everywhere Edition installed, because applications have quietly bundled SQL Server Everywhere Edition with themselves, they can push SQL Server Everywhere Edition out with an MSI install, and that new version will "win" over the private application versions.
It's also interesting to talk about the security aspects and risks, in historical comparison with our past products. If you look at the surface area of SQL Server Everywhere Edition, where we've traditionally had problems is where a product feature becomes a security hole. SQL Server Everywhere Edition, because it doesn't run as a service, isn't listening to network traffic. In fact, it cannot listen to network traffic. The surface area is dramatically different than our server SKUs, including SQL Server Express Edition. SQL Express does have networking turned off by default, but it can be turned on, and it does run as a service. Those features can be secured, but those features are still there. There's just less potential for vulnerabilities with SQL Server Everywhere Edition.
Another security consideration is code that runs inside of the database. Many databases let you embed code right inside of the database. But because SQL Server Everywhere Edition is in process with your app, it's in process with your code, so it's questionable why you would need to have code execute in the database. With SQL Server Everywhere Edition, you can't put code into the database. This also reduces the potential for vulnerabilities. There's nothing like SQL Server's xpCommandshell. There's no way the user can shell out through the database and do things outside of the scope of the app.
DDJ: So comparing that with Access, where a database can contain macros, and can therefore be potentially unsafe to e-mail around, a SQL Server Everywhere Edition database is safe to e-mail to someone because it's just data. There's nothing executable in it.
SL: Correct. There is no code in the database file, period. With Access and SQL Express, the database file can contain code, but with SQL Server Everywhere Edition it can't.
DDJ: One of the issues that comes up with in-memory database engines is that the data file can get corrupted, need to be repaired, and need to be compacted. Is that the case with SQL Server Everywhere Edition also? Is it that kind of database?
SL: You have to think about where SQL Server Everywhere Edition originated. It originated on devices. Devices get shut off, their batteries die, and you can sit on them and break them. SQL Server Everywhere Edition was always designed to be robust for that environment. But, yes, anything has the possibility of getting corrupted. I'm never going to say never. What's interesting is that when problems do happen, how do you go about fixing them? With SQL Server, you need to fire up SQL Server manager, and involve the DBA, whereas with SQL Server Everywhere Edition there's an easy API to perform those operations. This is another place where SQL Server Everywhere Edition is very developer focused. It's easy to code that logic into your application. There's a component called the SqlCeEngine that has a few APIs on it that provide the exact set of functionality that you're talking about. With those APIs, when you repair, you have the option to try to recover any transactions that were in process when it broke, or, if you can't recover those, you can purge those transactions.
DDJ: So how do people get started with SQL Server Everywhere Edition?
MJ: There's a preview edition (CTP) available today. People can go up to www.microsoft.com/sql/everywhere, where they can read more about SQL Server Everywhere Edition and pick up the bits, evaluate it, and develop against it. The launch date will be by the end of this calendar year.
DDJ: Thanks for taking the time to chat.
Terms of Service | Privacy Statement | Copyright © 2024 UBM Tech, All rights reserved.