Security
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.
