Information about SQL Server Compact Edition (SQLce)


On April 6, 2006 Paul Flessner announced SQL Server Everywhere Edition, know known as SQL Server Compact Edition.  http://www.microsoft.com/sql/letter.mspx  SQL Server Compact Edition a new SKU in the Microsoft product platform.  Paul announced that we would have a CTP in the summer of ’06 with final release in the 2nd half of ’06. 


So, what is SQL Server Compact Edition?  Here’s some Q&A that will hopefully help.  Some of this is still not finalized.  So take this information as our current thinking, not a final decision.  The purpose of this post is to give you our current thinking and solicit feedback. 


Q: Is SQL Server Compact Edition a new V1 product? 
A: No.  SQL Server Compact Edition is a new product name to acknowledge the new breadth of reach.  SQL Server Compact Edition is the SQL Server Mobile edition product with its limitation to the mobile platform removed.


Q: What’s SSEv and SSE?
A: Just my shortcut for SQL Server Compact Edition.  SSE is how I refer to SQL Server Express.  These are NOT official abbreviations, just my shorthand. 


Q: Should I be concerned about yet another V1 product?
A: No.  As noted above, SSEv is SQL Mobile with the license restriction removed for the Windows desktop platform.


Q: SQL Mobile and SQL CE were released on the Mobile platform, should I be concerned about SSEv and its stability on the desktop platform?
A: SSEv has been used on the desktop platform within several Microsoft products for years.  MSN Client, Media Center PC and several other products have already been using SSEv.  In order to test SQL Mobile and its predecessor, SQLCE, Microsoft has always tested the codebase using the Windows Platform.  While we do testing specifically on the mobile platform, most of our testing is actually done using the Win32 platform.


Q: Is this a modified version of SQL Express? 
A; No, SSEv is based on the SQL Mobile codebase.


Q: Will SQL Express be continued?
A: Yes, SQL Express is our free version of our server product, SQL Server. 


Q: Which database should I use for my desktop apps?
A: This is a longer discussion than what I’m trying to cover in this Q&A, but both SSE and SSEv will be available for the general desktop scenario.  However, SSEv is targeted specifically for general desktop usage.  It runs in-proc, doesn’t offer data as a service, has a lightweight model for applications that need to share the resources of the users machine with other applications besides the database engine.  SSE is a free version of the server product.  It runs as a service, and while SSE has been tuned to be more efficient about resource usage, it is a server based product.  General guidance is to start with SSEv.  SSE is well suited when you want exact functionality of the server platform, but are willing to deploy a server based product that will require significantly more resources than SSEv.  When considering local data stores, consider the bigger picture of the problem.  A users machine is a different operating environment than a server. 


Q: Will SSEv have the same licensing constraints as SQL Mobile?
A: No.  SSEv will be enabled on all Win32 desktop platforms. 


Q: Can I use SSEv as my web server database?
A: No.  SSEv is targeted at the desktop database.  Our plan is for SSEv to throw a not-supported exception when the hosting process is IIS.


Q: Why won’t SSEv be enabled for web applications.
A: SSev is targeted at client database scenarios.  The SQL Server SKUs, such as SQL Express are targeted as “data as service”.  By maintaining these two product lines as separate targeted scenarios we can enable features that would only apply to each scenario.  One size fits all means one size fits no-one.
SSEv will use the same set of classes that SQLCE and SQL Mobile have used.  System.Data.SqlServerCe.*  Because of this, if a website developer started with SSEv then wanted to switch to one of the Server based SQL SKU’s, such as SSE, they would have to change their ADO.net data access code. 
If a developer was building a client application and started with SSEv and wanted to scale their application to multi-user, they wouldn’t necessarily get rid of their local database.  The design we’re shooting for is for developers to keep their local database using SSEv and synchronize the data in their SSEv database with a server database using one of the various sync technologies.  Developers can use Merge Replication or Remote Data Access (RDA), a lightweight sync technology available to the SQL Mobile platform that will equally apply to the SSEv product.  In addition to these sync technologies, we are working on a new set of sync components based on the ADO.net programming model that we hope to ship in the Orcas product.


Q: Will Microsoft have a unified ADO.net provider?
A: We are considering various ways to abstract our different database products while minimizing the code a developer would have to change.  Rather than wait for a unified provider, we decided the client and data service scenarios are independently big enough to make the products we already have available for developers to start programming today.


Q: What is the feature set of SSEv?
A: For the ’06 release, it will be the same feature set as SQL Mobile. 
http://www.microsoft.com/sql/editions/sqlmobile/sqlmobile.mspx


Q: When will SSEv be available?
A: If you have Visual Studio 2005 you already have a version you can start developing against. 
http://blogs.msdn.com/smartclientdata/archive/2005/07/15/439008.aspx
 We plan to release a CTP of the SQL Server Compact Edition product at Tech Ed ’06 and RTM later this year.


Q: If the feature set of SSEv is the same as SQL Mobile, why do I need to wait ‘till June for a CTP and the end of the year for a final release?
A: While SQL Mobile has already been available for the Win 32/Tablet PC platform, and we already have a lot of experience with it in released Microsoft products, we have some installation decisions we want to finalize.  Our plans are to have a CTP of SSEv that have an installation model other than just copying the dlls to your application directory.


Q: SQL Mobile can be ClickOnce deployed without administrative rights.  Will SSEv have this same ability?
A: This is the main reason we’re not releasing immediately.  Because SQL Mobile can be deployed as private dlls, Microsoft doesn’t currently have a direct way to service these dlls in the case of a critical servicing issue.  We have several technical solutions that we are actively working on and we hope to be able to maintain this non-admin deployment model with SSEv. 


Q: Is SSEv suspect to the same threat that MSDE incurred with Slammer?
A: Well, never say never, but the main difference between MSDE and SSEv is MSDE and SQL Express run as a service.  While SQL Express has its port listening features disabled by default, it does run as a service and these features can be turned on.  Because SSEv doesn’t run as a service, doesn’t ever listen to network traffic, and doesn’t run without the hosting of an application, it doesn’t have the same “surface area” as its server based siblings.  Another key feature of SSEv is it doesn’t allow any code to be placed in the database.  It’s a pure data format.  Because of these differentiations, users should be much more comfortable with SSEv.  Although I’m unaware of any servicing issues in the SQL CE, SQL Mobile history, we do not want to assume we’d never have an issue.  Because of this, we are exploring several servicing options that will hopefully maintain the ClickOnce deployment model without requiring admin rights for installation of SSEv


Q: Can I use SSEv data files as my application document format? 
A: Yes, SSEv data files are pure data.  Unlike the server SKU’s, SSEv doesn’t have any code. No sprocs, views, triggers, extended sprocs, macros or ability to run XP_CmdShell.  Because of this, we do consider SSEv files a doc safe format. 


Q: Can I change the extension of SSEv files and associate that extension with my application?
A: Yes.  Because SSEv has a doc safe format, SSEv files can be emailed and their extensions can be changed to launch your application.  Visual Studio 2005 will only support .sdf file extensions, however, this is a limitation of the designers.  The SqlCeConnection object can handle any extension you wish.


Q: Does SQL Express support the changing of its extension? 
A: No.  SQL Express data file format, .mdf, contains code.  Because of this, Microsoft limits file based ADO.net connections to the MDF extension.  Users should consider .mdf files like exes.  They are just as capable of running code as an exe.


Q: How big is the SSEv runtime?
A: For all 7 dlls, the entire runtime is 1.4mb.  This is less than a SQL Express .mdf file.


Q: What is the data file size limitation of SSEv?
A: Both SSEv and SSE share the same 4gig size limitation.


Q: Does SSEv support multiple connections?
A: Yes.  One of the changes in SQL Mobile 2005 was the addition of multiple connection support.  You can now have several connections in your UI layer and another connection for background sync?  I believe the connection limit is 256 connections.  If you hit this limit, it would be interesting to know why? 


Q: Will SSEv support files placed on network shares?
A: Yes, but this is not meant as a multi-user scenario.  Because we know that users place their documents and settings directory on other servers we’ve enabled this scenario.  However, since SSEv is not a client/server database infrastructure the SSEv engine that opens the file will only share that file with other applications on the same machine.  If another user attempts to open the same data file from another machine the user will receive a connection open error.  We are working hard to make sure we don’t paint ourselves, or allow developers to paint themselves into a corner and travel down a dead end path.  SSEv is a user centric data store.  SQL Server is our data service platform.


Q: Does SSEv have a different set of data types than SQL Server?
A: No.  SSEv has a strict subset of the SQL Server Datatypes.  Developers that build their apps initially with SSEv will not have any issues converting their data up to SQL Server SKUs.  This was not the case with Jet to SQL Server conversions.


Q: Does SSEv have the XML data type?
A: No.  SSEv will place XML in an nText datatype when data is synchronized between SQL Server and SSEv.  So, you still have the XML storage, but SSEv will not have X Path query support in its engine.  This is something we’re considering for a future release.  However, the specific implementation has yet to be decided.  Our main goal is to keep SSEv as small and lightweight as possible.  The LINQ project opens up many interesting possibilities.  In future version we may leverage LINQ to get XML query capabilities without having to build an XML query engine directly into the SSEv engine.


Q: Does SSEv support CLR UDT data types?
A: No.  Because SSEv runs in proc with your application, the idea of hosting the CLR within the database would be a little redundant.  The database is actually hosted within the CLR.  You can place UDT data types in binary data columns, but just as with the XML data type, you will not be able to query into properties of these CLR UDTs.  Also like the XML data type, we may be able to leverage features of LINQ to enable this scenario without having to increase the size of the SSEv engine.


Q: Does SSEv have any unique features from its SQL Server products?
A: Yes.  Because SSEv is focused on local, in-proc data scenarios we can expose features that would otherwise be difficult to expose without scaling limitations.  SSEv will support the SqlCeResultSet api that was shipped in SQL Mobile 2005.  This feature enables developers to create a databindable, updatable, scrollable cursor.  As a user makes a change to a row, the values are immediately pushed back to the database. There’s no need for DataAdapters or DataSets.  SqlCeCommand includes some Seek like features that allow developers to leverage specific indexes as well.


Q: Does SSEv have Typed DataSet support and Drag & Drop support with the Data Sources Window?
A: Yes.  In Visual Studio 2005, developers can now create Typed DataSets for both their desktop and device projects.  For a walkthrough see:
http://blogs.msdn.com/smartclientdata/archive/2005/07/15/439008.aspx


Q: Can the same SSEv data file be shared between device and desktops?
A: Yes.  Since SSEv is based on the SQL Mobile codebase, developers can simply copy their .sdf file from the device to the desktop and back without any conversions.


Q: Does SSEv have a security model?  How does this compare to SQL Express?
A: Yes.  SSEv has a single user security model.  Because SSEv is targeted at per/user data scenarios, the assumption is the data is already partitioned to the user.  The application would provide the Username and Password in the connection string.  Because SSEv is targeted at desktop scenarios, there is no way to bypass the username and password authentication model.  While most would consider the SQL Server product line more secure, they would be correct for server scenarios.  However, the security model for the SQL Server product line is at the entry to the server, not the .mdf file.  Once an end user has access to the .mdf file, the user could use the SQL Express User Instance feature to connect to the mdf file as the DBO.  This bypasses any SA password that may have been applied to the database.  Therefore, under typical scenarios, SSEv will have a better security model that will limit users from viewing the data without your application, or the username and password.


Q: Does SSEv have any encryptions features?
A: Yes.  When creating a SSEv data file you have the option to encrypt the database.  While not nearly as granular as the SQL Server encryption features, this level of encryption provides security against users trying to prod into your data files without permissions.


Q: Can two users on the same machine share the same SSEv data file?
A: Yes.  While SSEv isn’t a data service platform, two users on the same machine would leverage the multi-user features.


Q: Does SSEv support multiple connections on different processes?
A: Yes. 


Q: Does SSEv support stored procedures, or views?
A: No.  Because SSEv is targeted at lightweight, in-proc scenarios these features aren’t available.  However, these features are typically used to provide multi-user abstraction from the underlying database schema.  Because SSEv runs in-proc with your application these features would typically provide more overhead than value.  Because SSEv runs in-proc with your application, why limit yourself to T-SQL within a stored procedure?  By creating a data access layer to your SSEv database you can use the full power of the .NET Framework. 


Q: Does SSEv support Triggers?
A: No.  Triggers do offer some interesting scenarios for eventing models.  We are looking at different models for implementing triggers that could benefit from an in-proc model. 


Q: Is SSEv available for native development with C++?
A: Yes.


Q: Is this a definitive list of features and decisions for the SQL Server Compact Edition product?
A: No.  This is our current thinking and baseline of the SQL Mobile product we are leveraging for SQL Server Compact Edition. 


Q: Does SSEv support Reporting Services?
A: Yes.  Reporting Services can build client reports based on DataSets or Objects.  Since SSEv has dataset and Typed DataSet support, developers can just as easily build a client side report for SSEv as they can any other data source.


Please send feedback on any and all comments.  Is there something I didn’t cover?  Let me know and I’ll add it to the list.


Steve


Comments (159)

  1. Стив опубликовал неплохой FAQ. Откуда произошёл SQL/e, отличия от большого SQL,

  2. Since the release of SQL Server 2005 late last year the mobile devices community have been discussing…

  3. Steve Lasker posted a FAQ sheet on SQL Everywhere  or SQL/e as he calls it. See http://blogs.msdn.com/stevelasker/archive/2006/04/10/SqlEverywhereInfo.aspx

  4. Mabster put me onto a post Steve Lasker has made a post about SQL Everywhere and I gotta say, to…

  5. Steve Kass says:

    You have referred to the new SKU several times as "SQL Everywhere".  In a number of places on the Sybase, Inc. web site, it is stated that "SQL Everywhere" is a trademark of Sybase, Inc. or its subsidiaries.  For example, see http://infocenter.sybase.com/help/topic/com.sybase.help.pb_10.5.apptech/html/apptech/legal.htm

    Has Sybase, Inc. released their trademark on "SQL Everywhere" to Microsoft?

    Steve Kass

    Microsoft SQL Server MVP

  6. Smartymobile says:

    Steve Lasker ha escrito un excelente FAQ (inglés) acerca de de SQL Server Anywhere aquí.

    &nbsp…

  7. wmugperu says:

    Steve Lasker ha escrito un excelente FAQ (inglés) acerca de de SQL Server Anywhere aquí.

     …

  8. Xentrax says:

    I’m happy! Perfect solution for storing user documents.

  9. DonS.CF says:

    On April 6th, 2006, Microsoft (via a SQL Server 2005 Update from Paul Flesner) announced a "new" product in…

  10. The Flyer says:

    Is it something like Firebird Embedded?

  11. Steve.Lasker says:

    I’m not familiar with the details of Firebird Embedded, so I can’t make a comparison.  To capture the essence: SQL/e is a lightweight, in-proc relational database that offers a subset of the data types and TSQL syntax to provide queries over relational stored data with transactional support.  The SQL/e data format can be password protected with encryption.  SQL/e is based on the proven history of the Microsoft SQL Server Mobile Edition technology.

    For details on the feature list check out: http://www.microsoft.com/sql/editions/sqlmobile/default.mspx

    Reporting Services Support

    I added a Q&A about Reporting to the above content.  Quick answer is YES.

    There were some questions about how much SQL/e makes a difference with the LINQ project.  SQL/e doesn’t compete w/LINQ.  SQL/e offers a better together story with LINQ.  LINQ doesn’t have a query processor. It takes VB and C# syntax and composes a query to pass to the underlying store.  The underlying store processes this query and returns a result to LINQ, or in this case DLINQ.  In comparison, when using LINQ over XML, there’s no query processor involved.  Large XML files wouldn’t compare in performance compared to leveraging a relational storage engine with a query processor.  Remember, XML files would have to be fully loaded into memory to process a request.  Relational databases have optimized indexes to only load the necessary information from disk into memory.  So, for the scenarios where developers are using XML, or specifically DataSets persisted to disk as their local storage, SQL/e would provide data integrity with the transaction support, security, and a significant performance boost.

    Steve

  12. Steve.Lasker says:

    Roger Wolter made a great post describing some of what we all went through in the VS Whidbedy product cycle.  He gave a great summary of the market for SQL Server Express and SQL Server Everywhere: http://blogs.msdn.com/rogerwolterblog/archive/2006/04/09/572101.aspx

    It’s worth a read…

    Steve

  13. Interesting product

    comparison between SQL Server Everywhere and SQL Server Express. Essentially…

  14. Hacked together a prototype for my Orcas feature over a weekend. Not bad for a PM,eh? :-). Sorry, I…

  15.  

       Steve Lasker posted a quick Q&A on a semi-new version of SQL Server called SQL Everywhere….

  16. Phylyp says:

    Steve,
    Forgive the newbie questions 🙂

    Is SQL/e accessible/usable only through code, or will it have support for a GUI (SSMS-E or a similar one)?

    Also, what are your views on using SQL/e rather than XML as a data store, for things like configuration, (BizTalk style) messages, etc.?

    Phylyp

  17. SQL Server Everywhere 追加情報(SQL MobileのWindows desktop 展開)

  18. OPC Diary says:

    Steve Lasker’s Web Log : Information abo…

  19. Aaron A. Anderson says:

    Why not just remove the usage restrictions of SQL Mobile in the interim?  If it’s good enough for Tablet PCs…

    I understand you wanting to controll service pack and critical updates, but having a per app instance would also potentially eliminate a bug introduced with an update.

  20. Smartymobile says:

    Steve Lasker ha escrito un excelente FAQ (inglés) acerca de de SQL Server Anywhere aquí.

  21. Eric says:

    How can we start developing with it now?

    – Can we use Mobile for development while waiting for the CTP?

    – Will our smart client prototypes work on XP if Mobile is used?

    – How much will break between Mobile and SQL/e? If we put thousands of man hours with mobile (while waiting for SQL/e), we need to protect our investment.

    – How reliable is the release date? We also have an "end of the year" committement date. There is some flexibility, but we need to control the risk level.

  22. Steve.Lasker says:

    Aaron,

    This is exactly the type of conversations we’re having.  SQL/e doesn’t have the surface area that a server based product does, so we are less concerned about it.  However, with the GDI+ bug, we’ve learned to never say never.  

    Since the api will remain the same, developers can get started today and we plan to hope have have a CTP of the install model by June 06.  

    Phylyp

    SQL/e is accessable through the standard VS and SQL Server Management Studio editors.  You can create databases, create tables, and in VS even edit data within those tables.  In comparison to XML for configuration, SQL/e would be a great replacement.  As configuration files get large, XML tends to become a problem.  In order to read XML you have to load the entire document in memory to navigate to a particular node.  Likewise, to save a single element, you have to read and save the entire document.  With SQL/e you can read/write one row at a time so it will scale much better.  While LINQ will make queering XML, Objects and Relational data easier, it leverages the underlying technology for query processing.  So, LINQ doesn’t solve this problem for XML.  

    Also remember that SQL/e has security and encryption built in, so rather then having to take on the ownership of these problems when dealing with XML, you can delegate this to SQL/e.  

    One of the things we’re considering is providing a Windows Forms Settings Provider that would use SQL/e.  We’re also looking to use SQL/e as the general cache for services on the client.

  23. Steve.Lasker says:

    Hi Eric,

    Yes, yes, nothing, …

    Something to recognize is that nothing is done until the software is in hand.  So, while we aren’t planning any changes to the runtime, dlls names, or scenarios beyond what I’ve listed in the Q&A, it’s really hard to commit to anything further.  If I said its firm and something comes up, what recourse is there?  We don’t know what we don’t know.  

    Based on the current installed based on Win32 we’re very comfortable with its stability and complete feature set.  We haven’t done anything in web environments and that’s not its target scenario so as long as you don’t assume this will work you’re on the safe line.  The thing holding us up is the install model.  Will Microsoft allow SQL/e to be installed by simply copying the dlls to your project as we do with SQL Mobile and Tablet PC?  Or will Microsoft require an MSI to install the product.  As with my previous post, we’re very comfortable with the security aspects, but we’ve also learned to never say never.  If we allow developers to deploy the dlls directly, Microsoft has no way to directly service those dlls in the event of a critical security servicing issue.  The problem is we don’t currently have a model that allows us to MSI install managed code without requiring Admin rights.  We consider this a big issue for enterprise developers.  What do you think?

    Steve

  24. Brett says:

    This is fantastic news to developers that use XML to transfer data back and forth to mobile devices like myself.  If its only going to be available as an MSI package please don’t limit the desktop install to XP SP2 as I find most large organisations (government mostly) still only have XP SP1 and in some cases 2000 SP4 and being a small development firm if we tell them they need to upgrade their OS to use our software their IT department’s would tell us to take a flying leap.

  25. Eric says:

    Steve,

    My 2 cents: if I deploy a desktop application which uses SQL Everywhere, I  will consider SQL/e a "component" of that application. If an upgrade is required I expect to do the same thing as with any other component: re-compile and re-deploy. Allowing to patch SQL/e on an existing application is scary since it does not change my build number. Support issues for sure.

    I am extremely familiar with SQL Server, but SQL Mobile is totally new to me. Basically, I am trying to understand if:

    a) the committement to the availability of that technology on the desktop is firm? If we invest a couple of millions in the next 6 to 8 months on this technology, I want to be on firm ground. I need to deploy in March 2007 at the latest, but probably a beta site in december 2006. Am I out of my mind?

    b) While waiting for the summer CTP, can we start development with SQL Mobile? Is the desktop deployment restriction a license issue only? Or will it prevent developing and testing on XP?

  26. Steve.Lasker says:

    Hi Eric,

    Thanks for the feedback on the deployment.  We’re actively working on how we’re going to approach this balance. As we close on a decision, I’ll post it to this blog.  I’ve opened another blog post related to the question: http://blogs.msdn.com/stevelasker/archive/2006/04/16/AdminInstalls.aspx

    Yes, you can use SQL Mobile today.  We are committed to releasing on the XP desktop.  This is what Paul Flessner announced on April 6th: http://www.microsoft.com/sql/letter.mspx so your not out of your mind.

  27. Nino.Mobile says:

      ..just a few tonight as I get back in the groove. My new project kicked off this week here…

  28. For those (like me) who have missed this announcement, SQL Mobile is now called SQL Everywhere, and guess…

  29. Rob says:

    I would be benefitial if you provide SQL/E source code under a shared code license so as it could be ported onto other platforms as well. 1.4mb is not critical code that Microsoft can’t help sharing or at least help porting onto other platforms.

  30. Hi and welcome to the SQL Mobile blog!

     

    The SQL Mobile team wants to make this blog a place…

  31. I’m inspired by experiencing the Outlook 2003 model of seemless offline data access, and I’d love to implement this feature into my own applications.  

    My desktop application currently uses Jet 4.0 as the database backend.  This has been ideal since my application typically only has one user connected to the database at a time.  Jet’s simple, inproc, single-file database engine has been great for my users.

    But now I want to develop an ASP.NET version of my application as well as a CF.NET version.  And I want these all to synchronize.  For my ASP.NET app I’d be fine with using SQL Server 2005.  For my CF.NET app I’d be fine with using SQL Mobile (Everywhere).  But the engine to use for my desktop app has always been up for debate.  SQL Server 2005 has dropped replication support for Jet/Access subscribers.  SQL Express is just too much for my app or my users to download and maintain, and it’s more of a 2-file system.  Thus, I’m thrilled to hear about SQL Server Everywhere.  I like that it is a strict subset of the full SQL Server, as I have a better chance of actually sharing some SQL code here and there.

    However, from my perspective, there are some features I’d be losing with a transition from Jet/Access to SQL Server Everywhere:

    1.  Multi-user access to a database file on a shared network folder.  I understand that this not a "best practice" for multi-user situations, but for non-technical users this provides a VERY simple solution for very small teams (ie. husband/wife on a home network).  Perhaps the artificial limit could be raised to allow connections from no more than two computers at a time.

    2.  The query designer.  I often use the visual query designer to more quickly write my SQL.  From what I can tell this is not available in SSMS for SQL Mobile databases.  It seems like this could easily be added to SSMS without touching the SQL Mobile engine.  It would also be nice if it would provide the SQL script for creating tables, etc.

    3.  Ability to link to tables from Access.  My application has many integration points with Office, and one such feature of my application generates a new Access database for my user with linked tables referring to my application’s database tables.  This gives my power-users a way to write their own queries and reports.  From what I can tell, Access 2003 can not link to tables in a SQL Mobile database.  Perhaps if an ODBC driver could be provided, this would be possible as Access could use ODBC for this.

    These are not necessarily show stoppers, but they would be a step backward.  I’m still strongly considering moving to SQL Everywhere.  But if these three shortfalls could be addressed the choice would become more obvious to me.

    Thanks,

    Troy

  32. Erik at HS says:

    Re: Can I use SQL/e as my web server database?

    A: No.  SQL/e is targeted at the desktop database.  Our plan is for SQL/e to throw a not-supported exception when the hosting process is IIS.

    – we currently have a scenario where we prebuild device databases via a web service and would be very sad if we are no longer allowed to do so – any thoughts ?

  33. Steve.Lasker says:

    Troy,
    Multi-User – The architecture limits our ability to make it multi-computer/user.  Multiple users on the same computer can share the same database, even at the same time as each database instance has a shared memory footprint.  However, the locking scheme prohibits us from enabling multi-computer.  Jet does this with a lock file external to the database file that the engine knows to look for.  This was the pro, and con for the stability issues as it’s really not a client/server database.  It’s a file shared database suspect to network drops and corruption.  So, I don’t see us doing multi-computer as this would be an architectural change and muddying the line between per user database/data and multi-user.
    However, using the new sync components, our goal is to make it very trivial for you to keep your household in synch with a master database.  And, yes, the master could actually be another SQL Ev database.

    Query Designer/Builder – If your talking about the query builder that is shared in VS and SSMS then this should work. The Query Builder is actually built by VS and is shared in many products.  There were some discrepancies in Whidbey that we’re working to rectify.  For instance you can edit data in SQL Ev within Visual Studio.  However, you can’t do this in SSMS.  Some of this will be fixed in SSMS SP2.

    Access / ODBC – Yup, this has been coming up a lot.  We don’t know when / how we’ll enable this, but we are hearing it quite often.  So, I’d like to say stay tuned, but honestly we have a lot of work to do in a short time to add a bunch of features to SQL Ev for the Orcas release.  And, of course, we’re still working on the initial release of SQL Ev.  We’re still on track for Tech Ed and I’ll post some details soon.

    Erik,
    Are you saying you already do this today?  I’m assuming you either have VS or SQL Server installed on the same box.  There are a couple of ways to solve this.  One is to use a Windows Service to actually build the .sdf files.  While we will be blocking ASP.net from using SQL Ev directly, it will be a soft block as we do know there are scenarios where developers have justifiable needs.  Honestly, we haven’t yet decided how to message the work around.  We’re really concerned about not creating the same IIS/Jet problems we’ve had in the past with frustrated users who start with Jet then don’t understand why it doesn’t scale.

    Thanks for the great feedback Troy, Erik and others.  The feedback posted has been helpful for me and the team to prioritize all the things we’d like to get done.

  34. Voici une très bonne nouvelle pour les développeurs de solutions mobiles : la base de données Sql Mobile…

  35. Troy Wolbrink says:

    > Multi-User – The architecture limits our ability…

    I didn’t realize this.  I’m a *little* more satisfied knowing it’s not artificial, but consider this a feature request (among many I’m sure).

    > However, using the new sync components … And, yes, the master could actually be another SQL Ev database.

    This sounds *very* intriguing.  I can’t wait to hear more about it!

    > Query Designer/Builder – If your talking about the query builder that is shared in VS and SSMS then this should work.

    I tried the designer in VS and it worked much better.  That’s good enough for me!

    > Access / ODBC – Yup, this has been coming up a lot.  We don’t know when / how we’ll enable this…

    Perhaps the Access team has/will enable this by adding support for linked tables via OLEDB or even ADO.NET drivers.  Isn’t ODBC a bit dated anyway?  :^)

    > While we will be blocking ASP.net from using SQL Ev directly, it will be a soft block as we do know there are scenarios where developers have justifiable needs.

    Here’s some ideas:  Have an “un-block” registry key or something you can add to the web.config or machine.config.  Perhaps even require a licensed SQL Server available, if losing sales is a concern.

  36. Steve.Lasker says:

    Access – We’re trying to figure out who’s going to support which side.  Honestly, it’s a little low on our priority list for now, but something that will definitely move up the stack.  The fact that Office and users would want to use SQL Ev for their scenarios means we’re right on target.  I’m confident it will happen, just a matter of how fast.

    IIS/ASP.net  Unblocking – we do have an “unblock” mechanism.  We just haven’t decided how to message it.  We basically don’t test or immediately intend to support multi-user web scenarios, but developers have single user asp.net scenarios.  Since there’s no way to differentiate these, we decided to “soft block”.  Make your case, a gift would be nice, and we we’ll eventually make the key a little less secret. <g>

    Steve

  37. Erik at HS says:

    IIS/ASP.net  Unblocking  – our case is that we currently have in production a mechanism to build SQL Mobile files server side for performance reasons. Hope that is specific enough…

  38. Lonifasiko says:

    Hi,

    I would like to know if we, Compact Framework programmers would be able to synchronize with SQL Server Anywhere using RDA or Merge Replication freely.

    What’s the expected launch date for SQL Server Anywhere?

    Thanks in advance.

  39. Steve.Lasker says:

    Lonifasiko

    We’re finalizing the dates for the launch date, but we’ll have a CTP ready at Tech Ed.  Final date is broadly stated as 2nd half of 06 as we want to leave a window open for feedback for releasing the bits.

    SQL Server Everywhere replaces SQL Mobile.  So, what ever you could do with SQL Mobile ont he device, you can do with SQL Everywhere including merge replication and of course RDA.

    What I think your question is, is whether SQL/e can be a publisher.  The answer is no for RDA and Merge.  But it can for our future sync components we’ll be shipping in Orcas.  So, you’ll be able to sync from SQL/e on the device to SQL/e on the desktop.

    Did I get your question correct?

    Steve

  40. Steve.Lasker says:

    Erik, pleaes contact me directly: http://blogs.msdn.com/stevelasker/contact.aspx

  41. Jeff Bowman says:

    Regarding redistribution rights, will licensing for small-footprint ISV retail applications built on SQL/e be royalty-free?

  42. Steve.Lasker says:

    Hi Jeff,

    SQL/e is free for one, two or two thousand copies.  Of course, if you want to pay us, I’m sure we can arrange something <g>.

    To be complete, SQL/e doesn’t have any licensing fees.  However, SQL Server does.  So, if you’re using SQL/e to sync with SQL Server, that’s when you’ll need a client access license CAL for each client synching.  SQL Server just considers this another connection, so it doesn’t matter if it’s SQL/e, a web client or a smart client, they’re all just clients.

    Steve

  43. Hey,

    I know I don’t blog much so heres a few things that you probably saw if you read my comments, but…

  44. Steve Clark says:

    Just ran across your info about SQL/e and was excited to here about an alternative to Access and SQL/x. We currently use Access in our VB6 application.  However, many of customers share the .mdb accross a network, which, if I’m reading correctly, SQL/e would not support. We’re not thrilled at the prospect of installing and running SQL/x on each of their desktops.  

    I’m not sure I understand your statement:
    “We are working hard to make sure we don’t paint ourselves, or allow developers to paint themselves into a corner and travel down a dead end path.”

    I would love to have “corner” that included SQL/e that could access the database file over a shared network folder.

    Any new SQL/e developments in this area?

  45. Steve.Lasker says:

    Hi Steve,

    The problem is SQL/e is designed and optimized as an in-proc, multiple connection, lightweight database.  It’s not a client/server, multi-user database service that knows how to remotely service requests.  Jet does enabled users to share mdb’s across the network, and while it has been productive, it’s also been one of the biggest source of problems.  Rather then taking the one-size-fits-all approach, with the release of SQL/e we are working to divide and conquer the local in-proc database scenario and the multi-user client server database.  

    Considering the footprint, absolute minimal impact to your desktop machines, I’m curious why you’re not thrilled about installing SQL/e on each machine.  Even if we supported shared .sdf files on a network share, we would still require SQL/e to be installed on each machine.  That’s the nature of an in-proc engine.  This is the same for Jet.

    It’s worth noting that while we don’t support, and don’t intend to support, -shared- .sdf files on a network share, you can place the .sdf on a file share for single user usage.  The first machine that opens the .sdf file will gain a lock that will block other machines from opening the file.  This enables users who share their personal documents on a file share.

    Steve

  46. Steve Clark says:

    Steve,
    I think you misread my statement: We’re not thrilled about installing SQL Express on each of our customer’s desktops. We may end up doing it if we have to.  Many of customers have a small number of workstations networked together with one acting as a server in the sense that it shares folders and files. In this environnment, it seems a bit much to install and run a service on each desktop to access a common data file. But I might be mistaken here  🙂

    The sync features sound interesting. Are they available now? Could you have SQL/e equipped workstations syncing to another SQL/e equipped workstation sharing the .sdf?

    Thanks,
    Steve

  47. Steve.Lasker says:

    Steve, I think your getting SQL Express and SQL Everywhere confused.  You’re not the first; we trip up in meetings all the time <g>

    SQL Everywhere doesn’t run as a service.  It’s simply several dlls, totally less then 1.6mb.  If you exclude the 227k ADO.net provider for SQL/e the entire SQL Everywhere runtime fits on a floppy.  

    When you install SQL Everywhere it simply copies these dlls to the %ProgramFiles%Microsoft SQL Server Everywherev3.1 directory.  It then GAC’s the ADO.net provider so it can be loaded by managed apps.  There’s no service running, EVER.  If you’re not using SQL/e as part of your app, it isn’t running.  It can’t run as it can only run if it’s loaded in-proc by an application.

    So, the impact of installing SQL Everywhere is about as minimal as it gets.  

    Now, for your small networks where you want several desktops to share a common database, then yes, that’s the SQL Express scenario.  You would install SQL Express on that one, and only one workstation. Each client can connect to that single workstation.  In this case you likely wouldn’t have a local database on each machine as they’re all sharing the common SQL Express.

    SQL Express is the free Data as a Service platform.  SQL Everywhere is the single user data storage solution.

    New sync features.  We don’t have anything released yet.  I’ll have some stuff to demo at Tech Ed, but nothing to share just yet.  Yes, you’ll be able to sync between SQL Everywhere and a central SQL Everywhere database.  In this case SQL Everywhere still isn’t being exposed as a service, rather you would configure your machine to expose Sync Services that would then load SQL Everywhere.  While this is possible for some scenarios, our mainline scenario is to use SQL Everywhere on the client and sync with SQL Server (Express, standard, pro, …)

    Now, just to fill in one more scenario, you can open an .sdf from a file share.  However, the first machine that opens the file places a lock on it, and no other machine can open the file until the first closed the connection, thus releasing the lock.  Because SQL Everywhere runs in-proc, the hosting machine literally opens the file and does the locking behavior.  Jet has a lock file they place on disk, which while empowering has all sorts of issues.

    Hope this helps,

    Steve

  48. Another fantastic event kicked off in Nice, France today at the Acropolis.

    Todd Warren delivered a…

  49. If you’re developing Line-of-Business (LOB) applications that have needs beyond the cool stuff provided…

  50. SQL Server 2005’s Express Edition is free, it supports database sizes up to 4GB, it allows seamless upgrades…

  51. VSTE4DP(Visual Studio Team Edition for Database Professionals) – 얼마전에 발표한 VSTE4DP의 CTP.

    Biztalk Server…

  52. Diego Vega says:

    Steve, when you say “Our plan is for SQL/e to throw a not-supported exception when the hosting process is IIS”, there is an issue that comes to my mind. Right now we are working on an application that needs to run on the web in multiuser mode, but also needs to run stand alone in disconnected scenarios. Because of costs concerns we decided that we would only develop a ASP.NET 2.0 version of the application and then would leverage this same version on local IIS for stand alone mode. I see that such an scenario would not be supported by SQL/e. But in any case, don’t you think it would be interesting to pair SQL/e with Casini? I think this would be an interesting lightweight duo.

  53. Sam Gentile says:

    Not much today.

    WCF/SOA/WSE

    The WCF RSS Toolkit is a Windows Communication Foundation-based framework…

  54. SQL Server 2005 Everywhere Edition is the&amp;nbsp;followup to&amp;nbsp;SQL Server 2005 Mobile Edition (SQL Mobile).&amp;nbsp;&amp;nbsp;…

  55. StWy says:

    How can we access the Sql everywhere from the management studio?

    The only servers I can get is localhostSQLEXPRESS

  56. matt.tester says:

    It’s a shame about the restriction with using it for ASP.NET applications. I can understand the reasons as they’ve been described but there is surely a case for Sql Everywhere being used for simple websites, where a full SQL Server hosting plan is overkill.

  57. Phylyp says:

    For what it’s worth, I support your decision to not support multi-computer access to SQL/e databases.

    I once built a Jet solution that was accessed by multiple computers, and watched it *sink* when about 50 users hit it.
    I then had to introduce a connection-pool between the client and the database, basically turning a simple two-tier app into a three-tier one!

  58. Hi and welcome to the SQL Mobile blog!

    &amp;nbsp;

    The SQL Mobile team wants to make this blog a place…

  59. Marcin Kosieradzki says:

    Hi,

    Is SQL/E RTM going to support Windows 2000? CTP version supports only XP SP2+, but this post states that: “SQL/e will be enabled on all Win32 desktop platforms.”.

  60. Steve Lasker has posted a screencast / video over on Channel 9 providing an overview on the various ADO.NET…

  61. Steve Lasker has a stream of information about this new technology. Start with his Info post and work…

  62. (this post was originally posted here)

    Steve Lasker has posted a screencast / video over on Channel…

  63. acid says:

    Is SQL/e support Windows XP Embedded OS?

  64. Hans says:

    This is wonderful, highly appreciated stuff.

    You encouraged us in the Channek 8 video to send requests for Xml support.

    Yes: please do! Xml will be crucialfor a more object orientated use: With Xml support, code revision and maintenance of applications by others will be significantly enhanced.

    Hans

  65. crifo says:

    Hi Steve,

    I must to realize a database application for an Automation system consisting of a Industrial PC with Operative System microsoft windows embedded.

    1° question: Is Microsoft sql server 2005 everywere compatible for Microsoft windows embedded?

    (I ask this because in specific of its is not clear)

    2° question: In the case that it’s compatible,  if I instal it in a industrial Pc can it to act by database server or necessity of a remote server?

  66. Data says:

    So, what is ‘SQL Server Everywhere Edition’?

    Answer?

    Check out this Channel 9 video with Anil Nori…

  67. With all this excitement about SQL Everywhere, i just wanted to post about another exciting database release for .NET and Compact Framework developers — VistaDB 3.0.

    We just released VistaDB 3.0 CTP, which unlike SQL Everywhere, is the world’s first fully managed and typesafe SQL database engine designed for .NET and the Compact Framework. VistaDB 3.0 will run on fully managed on .NET, 64-bit .NET Compact Framework, Mono, and Windows Vista. Lots of features and interesting differences between VistaDB 3.0 and SQL Everywhere — we hope .NET and Compact Framework developers take a serious look at what we built.

    Comparison of VistaDB 3.0 vs SQL Server Everywhere:
    http://www.vistadb.net/compare_sql_everywhere.asp

    Screenshots:
    http://www.vistadb.net/screenshots_vistadb3.asp

    VistaDB 3.0 info:
    http://www.vistadb.net/vistadb3.asp

    Thanks for letting me post to your blog.

    Anthony Carrabino
    http://www.vistadb.net

  68. Stew Davis says:

    I understand your point about mdf file, but if the file is not encrypted in SQL Everywhere what would keep someone from reading the native file format and extracting the data. If they have the wherewithal to know to copy and attach the mdf file than it would be likely that they will know how to read an unencrpted SQL Everywhere file (via some download if they don’t have the programming ability). Also, while the SQL Express service is running a user will not be able to copy the file. I assume this is not the case SQL Everywhere.

    Is the above accurate?

    What are the performance issues associated with encrypting the SQL Everywhere file?  

    How much of the SQL 2005 TSQL will be supported?

  69. Steve.Lasker says:

    Hi Stew,

    Everywhere Edition files can be encrypted in two ways.  One, you can simply encrypt the file on the connection string.  If you don’t have the password, then you can’t parse the file.  However, because securing a password is near impossible on the client, SSEv supports the features of the Windows Encrypted File System (EFS).  With EFS enabled on a file, users are unable to copy the file off the machine if they aren’t the user.  If they are the user, they are prompted that copying an EFS enabled file will loose its encryption.  The main point about SSEv vs. SQL Express (SSE) encryption comparisons, is the features of SSE encryption are based on the typical server environment.  Once you’re “on the box”, it’s relatively easy to circumvent the security features.  SSE does support EFS when used with the User Instance features, but not under standard connection features.  When running with User Instance, the service is running as the user, so it has the necessary rights to open the database as it’s encrypted as the same user as the service.  This isn’t the case when the database is running under the main instance.  When running with the User Instance feature, the database isn’t attached until the application is running.  So, the rouge user can copy the file.  Security is only useful if properly implemented.  The more complex the model, the more likely it won’t be properly secured.  The issue here is while SQL Server has some of the best security features in the industry, they are geared around server scenarios where you connect to the server remotely.  SSEv was designed where the data file is always in the hands of the user.

    Copying files

    You can copy a SSEv data file while it’s open. This is actually the backup mechanism.  However, as noted above, if using the User Instance Features of SSE, you can copy the MSF file if the app isn’t running.

    Perf

    We haven’t published perf numbers for SSEv yet as we’re still optimizing for desktop scenarios.  This is one of the reasons we’ve been holding up the initial SSEv desktop release.  However, with SSEv, we don’t consider TSQL throughput the top feature.  We think of performance of the overall machine.  However, with that said, we are making sure that SSEv performance numbers are very good.  

    Steve

  70. Steve.Lasker says:

    Crifo,

    Windows Embedded:

    Like Win2k, we do believe we can support Windows Embedded.  However, there are enough differences that the QA team wants to run some test passes before committing to it.  So, the answer is, it should work, we would most likely fix any bugs, but we’re still finalizing how official we’ll be on Windows Embedded.  …stay tuned.  

    Remote connections:

    I’m not sure I understand the question, but I’ll answer it this way.  Let me know if you meant something else.  SSEv does not listen to network traffic.  There is no hosting service.  However, you can do one of a few things.  You can write your own Windows Service to expose it.  You can also connect to the file by sharing the file sharing.

    Steve

  71. srinsoft says:

    Hi

    We are devloping a database management utility for MSDE,sql server enterprise and sql EV

    There are system queries available for performing index rebuild and setting minimum and max memory on msde and sql server by using the following statements below

    "DBCC DBREINDEXALL(S)"

    "SP_configure ‘min server memory’,1024 Reconfigure"

    "SP_configure ‘max server memory’,6144 Reconfigure"

    But is there a way I can perform the same on SQL Ev. I tried using the above statements, but it doesn’t wor out, can anyone help me with statements specific to SQL Mobile or Ev.

  72. JazzyJ says:

    Is SSEv thread safe?

  73. Steve.Lasker says:

    Yes, SSEv, as an engine, is thread safe.  Multiple processes, appdomains, apps, can all connect to the same SSEv data file at the same time.  

  74. Shloma says:

    Hi

    The main problem for me is, that SQL Server Express is too big of a download and takes too long too install so I was hoping that the SSEv will help me here, but since it dosn’t support sprocs, views and UDF’s its a real problem for me, did anything change in this regard since you wrote this blog?

    Regards,
    Shloma

  75. Steve.Lasker says:

    Hi Shloma

    No, we do not plan to support code in the database, which includes Sprocs and UDF’s.  Views are a bit interesting, but we believe the ADO.NET entity framework will provide the abstraction that views provide and more.  

    It takes folks some time to think about a local database differently.  Simply put, when you have a database in-proc with your code, why would you limit yourself to the TSQL language.  You can do a lot more with managed code than you can accomplish with TSQL.  And, that assumes we have full SQL Server TSQL support.  There’s a reason the engine is so small, we have the focused features for an in-proc database.  

    What would you expect from sprocs?  Abstraction?  The database is part of your app?  The typical abstraction layers required between the database and multiple apps just don’t apply.

    Performance?  While we could sneak some performance for caching a query plan, we’d likely loose it for the extra "weight" of supporting sprocs?

    UDF’s?  Again, what about using a function in full managed code?  With LINQ, and the ADO.NET Entity Framework, we have a more powerful features at your hands.  …or you will…

    Views?  Aggregate multiple tables in a single view?  Views typically only update one base table, …sometimes.  With the Entity Framework you can get the aggregation, and all tables are updateable.

    We appreciate the need for wanting the same programming model locally when compared to the server.  However, sometimes the uniqueness is something to leverage and embrace, not eliminate.  

    We are working on a white paper to try and explain the Express and Everywhere target scenarios.  We hope to address the sprocs issue as well.  

    In short, we consider Everywhere Edition the default local database choice for local storage.  Express may be used for local storage in extreme, but rare cases.  

    On the other hand, If you’re locating data on a central machine for use by multiple people, then Express is your starting point. Express is our free, feature rich data as a service platform.  

    A perfectly valid scenario is Express on the central machine, and Everywhere Edition on the local machine.  

    Steve

  76. OS says:

    I’m getting a protected memory access error at times while using the product in a multi-threaded application.  What is the best forum to post this issue?

  77. OS says:

    This product should be called SQL Server InProc and the code base should be split from the mobile version.  Why?

    In a short time, the number of users of the desktop version of SqlServerCe will far outnumber the Windows CE based users (a quick review of ms revenue splits by product division provides evidence for this).

    Desktop machines have and will always have more capability than mobile machines.  By splitting the code base you could add more features for your primary audience (desktop developers).  Currently in SqlServerCe many basic SQL features are not available (SELECT TOP) multiple result sets, etc (sprocs would also be helpful but they should be restricted to operate on data within the database file only for security reasons).  For the CE case it makes sense to leave these out because you are operating in a constrained environment.  For the desktop version, doubling the size of the installed binarys would be of absolutely no consequence.

    Furthermore, why should 95% of the users of this product have to use a somewhat confusing namespace “SqlServerCE”/”Microsoft SQL Mobile”?  

  78. Matt says:

    Will there be any support with BCP or similar tools to automate import or export of data from flat/text files?

  79. Chrsitian Melançon says:

    Hi,

    I was wondering if SQL Server Everywhere CTP or its final release will be 64-bit compatible?

    Thanks!

  80. Steve.Lasker says:

    Hi Christian,

    SSEv will have native 64 bit support, however, it’s a bit out and won’t be in the 3.1 release this year.  Our current plan is to release 64 bit support in our 3.5 release next year.

  81. Steve.Lasker says:

    Hi Matt,

    We do have some tools work in progress, but that’s also a bit out and planned in the 3.5 release.  It’s a longer story, but shipping products at Microsoft has a lot of "taxes".  We have UI guidelines, accessability, globalization and localization, many platforms to support etc.  The dev work is actually the easy part.  

    I don’t have any links handy, but I have seen the community and partners develop tools based on the SqlCeResultSet api that are very fast.

  82. Steve.Lasker says:

    Hi OS,

    Names, names, what’s in a name anyway?  At the end of the day, do you really care what the name is? <g>  Or do you care what it does?  

    We are adding additional query features in the 3.5 release, so some of these features will be coming.  

    As for the namespace, we purposely didn’t change it as we didn’t want to break any existing code.  One of the main values is the ability to share code from device to desktop.  This is also why we don’t want to introduce yet further differences between the device and desktop versions.  

    As for size, well, even though SSEv is a fraction of the size of Express, in many cases it’s still too big, even for desktop and we are doing some factoring work that will reduce the overall sizes further.  …I’m talking about future releases past 3.1

    Steve

  83. Steve.Lasker says:

    Multi-threaded support-

    It would be great to get some repro code.  The team monitors this forum:

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=152&SiteID=1

  84. Neal Davis says:

    I downloaded the sample Northwind.sdf database, it has spaces in many of the column field names? However the Northwind.mdb and the SQL Server Northwind database samples do not have spaces in the column field names. Why did they put spaces in the Northwind.sdf database field names? I have always been taught that is really "bad" it also makes it hard to write SQL statements in code – I guess you have to bracket each field name that has a space? example INSERT INTO Employees ([Employee ID], … is that how you handle spaces in field names for SQL Everywhere northwind.sdf?

    But I am very excited about SQL Everywhere I hope Microsoft makes it a replacement for Access and gives it limited multi-user connections (like maybe upto 5 or so) and would like multiple connections from different processes which I think I read you can do! Great work – this is what I need for my applications and I need both native and managed capability.

    Neal Davis

  85. Cassio says:

    Hi Steve,

    My company have a smart client application that uses cached data in XML files for offline access. These files are getting larger each day and to query it using DOM is causing performance problems.

    So I started playing with SSEv to see if we would have any problem to use this aproach instead of XML files.

    Everything worked great, except for one thing. Let me explain the scenario.

    Whenever we have to change the data schema, the smart client application have to download the whole XML files again. Because the XML files are now 50MB large, the server side uses a zip compression algorithm that reduces these files to 4MB, the client app downloads the file and decompresses them. We use Agile, so these schema changes are not so rare.

    Testing with SSEv the .sdf file size was around 23MB, but there was no way to compact it. I tried the Compact() and Shrink() methods with no success. I even tried using WinRar, but the file size remained the same.

    Is there any special way to compress the .sdf file size?

    Thanks,

    Cassio Alves

  86. David says:

    It has been a while since I have done any SQL Server work, but I was wondering how I would retrieve identity values without stored procedures?

    Also, without SProcs, there is a real burden put into the code side for any non-trivial transaction programming. I would need to code my multi table updates through ADO and block transaction them in code.

    Or am I missing something?

    Thanks

    David

  87. Steve.Lasker says:

    Northwind Spaces:

    In one sense I agree, and think spaces in column names is just a bad design and leads to all sorts of problem.  However, for better or worse, it is supported.  Our sample databases are typically built to help test out the features in the product.  That can go overboard in many ways.  The Northwind sample for SSEv, or the AdventureWorks sample that ships with SQL Server 2005 which had feature soup and just went overboard as well.  

    The teams are working on a simplified, more realistic version of AdventureWorks.  We’re hoping to have that sample available for SSEv as well.

    Steve

  88. Steve.Lasker says:

    XML Compression:

    Hi Cassio

    I’m not sure if you’re asking if inserting XML data can be compressed, or just how to use the Compress API on the SSEv engine.

    To compress the database:

    Close all connections and call SqlCeEngine.Compact (<new database name>)

    Change the value of auto shrink threshold to 10% in the connection string.

    Compact will make a copy of the database in a compressed format.  SqlCeEngine.Shrink() will clean up deleted pages in the same database file.

    Since xml file is almost like a text file, compressors like winzip, winrar can apply their text compression algorithms. That’s the reason why you’re seeing a good amount to size decrease in your XML file. When the same data is stored in our database, we don’t store them in plain text. So these compression algorithms cannot play a huge role here.

    Steve

  89. Oli says:

    Any news re the release date? Thx, Oli

  90. SQL Server 2005’s Express Edition is free, it supports database sizes up to 4GB, it allows seamless upgrades

  91. Hi and welcome to the SQL Mobile blog! The SQL Mobile team wants to make this blog a place where we can

  92. gabe says:

    I’ve been looking for GetSchema support (System.Data.Common.DbConnection) in SqlCE, but I’m not seeing it.  When I attempted to execute GetSchema I recieve: System.NotSupportedException: Specified method is not supported.

    Is this something I can look forward to in the 3.1 rtm?

  93. Arasu says:

    Can someone point me to a C++ example for SSEv?  (not .NET) – want to learn about moving a JET database to SSEv.

  94. WishfulDoctor says:

    Apologize in advance if this is not the place for this question & for a newbie question to boot.

    Can I use SSEv (Compact Edition) as a replacement for JET for a native c++ application. The (unmanaged, not .NET )application uses ADO to connect to the JET database & uses MDAC & JET components already in Windows. With Vista, there is no JET & I want to know if I can use SSEv.  All the examples and discussions are ADO.NET & I cannot now move to .NET.  Do I need to?  The way I read the material so far, all I need to do is copy the needed dlls & register them, convert my .mdb to a .sdf file, change the connection strings and I should be in business.  Is it as simple as that?

    Once I again, I apologize if this is not the right forum – just point me to where I can get help.  By the way, the folks at MSDN managed forum for Database say that they do not support SSEv.

  95. I have recently joined with a project that looks for a In-Memory Database for client-side caching solution.

  96. Dave Goldstein says:

    I miss most of all the ability to define views.  I do not understand your comment that it is for "multi-user abstractions".

    Most uses of views encapsulate complex queries or joins or computed columns, so that these details can be encoded once and then reused everywhere.

    Views that roll-up or join data also allow high level tools (like grid widgets) to be given (what appears to be) a simple table name instead of a complex query.

    If we need to give up some view features (like updatable views) in order to have them at all, that would still be a great start!

  97. Hi and welcome to the SQL Mobile blog! The SQL Mobile team wants to make this blog a place where we can

  98. Steve.Lasker says:

    Hi WishfulDoctor,

    The answer to your question is yes, but not as clean as we’d like.  There is full support for SQLce in native code, but full doesn’t necessarily mean consistent with MDAC.  The RecordSet model isn’t fully supported in the native stack of SQLce, so while you can use SQLce from native code, the communications are a little lower level.  There’s a little info here in this blog post from the team: http://blogs.msdn.com/sqlservercompact/archive/2007/01/13/windows-2000-support.aspx.

    We will continue to support native development, but don’t have official plans on adding support for the recordset programming model of MDAC.  While I’m focused on managed development, I can’t help but point out that Vista comes with .NET FX 3.0, but that’s just a plug from the Developer Division.  

    Steve

  99. Steve.Lasker says:

    Hi Arasu,

    Here’s some samples posted by the SQLce team:

    http://blogs.msdn.com/sqlservercompact/archive/2007/01/13/windows-2000-support.aspx

    The topic says Windows 2000, but within the post, there’s a link to:

    http://msdn2.microsoft.com/en-us/library/ms173261.aspx

    Steve

  100. Bob Li says:

    SQL Server Everywhere (SSEv) 是 SQL Server Mobile 2005 (SSM) 的下一个版本,版本号为 3.1,而 SSM 的版本号是 3.0。SSM 支持 Windows CE、Pocket PC、Smartphone 和 Tablet PC,而 SSEv 还增加了对 Desktop PC 的支持,再一次把移动数据库推向一个新的高潮。

  101. Neal Davis says:

    Steve;

    Do you think Microsoft will eventually create a UI application for SSCE like it has for Access? That is, an application that allows you to easily create tables, columns, rows, etc. just like the MS Access application allows you to do with .mdb files?

    Neal Davis

  102. Steve.Lasker says:

    Hi Neal,

    Do you need yet another application to do this?  Today, we have Visual Studio and SQL Server Management Studio for developers/DBA’s.  If we enabled Access to work over SQLce, would that be what you’re looking for?  Essentially, the answer is we don’t want to create yet another application, but rather enable SQLce within the existing tools and environments.  

    Steve

  103. Matt says:

    When creating a SQL Server Compact database/connection initially on a .NET application, the engine appears to hang for about 15 seconds.  This happens only once when the database components are initially used, future connections or database activity cause no delays. I also see this delay when using Microsoft SQL Server Management Studio to connect to SQLCe databases.  Is there a way to eliminate this initial delay?

  104. Steve.Lasker says:

    Hi Matt,

    I can’t say I’ve seen this.  Typically, CE connections are near instantaneous.  Could you confirm you’re using SQLce 3.1 http://microsoft.com/sql/compact ?  

    It would be good to get some info on your environment, OS, etc.  Please feel free to send to my email via the link in the top left corner.  

    Steve

  105. One of my favorite features in Orcas is the ability to leverage the ASP.NET Application services from

  106. Since the release of SQL Server 2005 late last year the mobile devices community have been discussing

  107. Sam Gentile says:

    Not much today. WCF/SOA/WSE The WCF RSS Toolkit is a Windows Communication Foundation-based framework

  108. Robert says:

    How can I open (in an application) a protected .sdf database ; I used this code but doesn’t work:

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

           Dim objConn As New SqlCeConnection

           Dim pwd As String = “RMN123!@#”

           Dim connStr As String = “Data source=My Documentsb1.sdf; SSCE:Database Password=pwd”

           objConn.ConnectionString = connStr

           objConn.Open()

        Me.AG1TableAdapter.Fill(Me.B1DataSet.AG1)

    If you can help me send an email at radunicolae1@yahoo.com

    Thanks

  109. Steve.Lasker says:

    Hi Robert,

    Your hitting a common string concatenation issue.  Your code above is setting the password = to the literal value; pwd, rather than the value of the pwd variable.

    Dim connStr As String = "Data source=My Documentsb1.sdf; SSCE:Database Password=" + pwd

    Or String.Format("Data source=My Documentsb1.sdf; SSCE:Database Password={0}", pwd)

    Steve

  110. Robert says:

    I put the string like you said and I thank you Steve , but if I do not comment the last line ( ‘Me.AG1TableAdapter.Fill(Me.B1DataSet.AG1) )

    I still get the error : "The specified password does not match the database password"

    ____________________________________________

     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

           Dim objConn As New SqlCeConnection

           Dim pwd As String = "RMN123!@#"

           Dim connStr As String = "Data source=My Documentsb1.sdf; SSCE:Database Password=" + pwd

           objConn.ConnectionString = connStr

           objConn.Open()

        Me.AG1TableAdapter.Fill(Me.B1DataSet.AG1)

       End Sub

    ————————–

    I verified the password for the database (b1.sdf) by opening it directly (‘double click’ on the pocket-pc and manually providing this password : RMN123!@#   )

    Thank you for your advice and understanding

  111. Robert says:

    Maybe the last line is not in relation with the others because I still get the error message "the specified password does not match the database password "

    _____________________________

    Dim objConn As New SqlCeConnection

          Dim pwd As String = "RMN123!@#"

          Dim connStr As String = "Data source=My Documentsb1.sdf; SSCE:Database Password=" + pwd

          objConn.ConnectionString = connStr

          objConn.Open()

       Me.AG1TableAdapter.Fill(Me.B1DataSet.AG1)

    ——————————————

    Thank you for your answer

  112. Steve.Lasker says:

    Hi Robert,

    I see your using a new SqlCeConnection, setting it up, then calling Me.AG1TableAdapter.Fill.  What’s happening is the TableAdapter doesn’t automatically associate with the new connection you’re creating.  If you look in the Settings Designer, you should see the connection string the TableAdapter uses and can set it there.

    You can also set the ConnectionString on the TableAdapter.  That will get them together and it should work.

    Steve

  113. Robert says:

    Thank you, setting the password works fine.

    Now I have another question:

     is there any possibility to protect a pocket-pc application ?

    That means when somebody (unauthorized) tries to copy the application (wich consists of these two files – the database.sdf and the executable itself) to get the message "Sorry, you don’t  have rights to use this application…". I saw that exists a function like : IOCTL_HAL_GET_DEVICEID but I also understood that not all of the pocket-pc support this .

    I try to read at the begining of the application the "Device-ID" of the pocket-pc; if this is the one where I installed the application -continue , otherwise-Exit application.

    Thank you very much for your help

  114. Robert says:

    Hi Steve,

    now comes the more difficult part for me ; I want to make an application which uses both a desktop (or two ,in a network) PC and 4 Pocket-PC’s ; to be more concrete it’s about registering on Pocket-PCs the orders made by customers in a little resturant at each table by a waiter and transmit the orders from these 4 tables to the Database on the desktop PC which could be placed in the kitchen,for example (like a client-server application); What do you sugest me to use in this case ; can I use Visual Basic 2005 to make this kind of application ? What database software do I need exactly (for PC and for Pocket-PCs); I did’t see the relation between a .mdf file and a .sdf file  (like it is when I use Access – with .mdb file , and when transferring on Pocket-PC it is transformed by conversion in a .cdb file with same data); can I do a transfer from a .sdf database (pocket-pc) to a database on a desktop PC ?

    I also intend to make the connection (synchronization) by WiFi.

    Thank you very much for your help.

  115. Robert says:

    I managed to resolve the problem with Device_ID

  116. ROBERT says:

    Hello Steve,

    How can I restructure a .sdf SQL CE database ? (inside Visual Studio 2005 I didn’t find any possibility)

    Thank you for your answers

  117. ROBERTO says:

    Hi Steve,

    How can I open another form from the first one in an application with a SQL Server Compact Edition database (from Form1 I cannot find the function to open the Form2 ) ; Form1 is for selection (Query) and Form2 is for entering new data in the .sdf database

    Thank you very much

  118. Steve.Lasker says:

    Hi Robert,

    by restructure, do you mean edit the schema of the tables?  VS 2005 supports tooling for SQLce 3.1.  VS 2008 supports tooling for SQLce 3.5.

    Steve

  119. Steve.Lasker says:

    Hi Reberto,

    Interacting with forms can be done in a few ways.  VB added the My namespace in VS 2005 so developers can simply say My.Forms.Form2.Show()

    Both VB and C# can use the traditional .NET object instantiation of:

    VB:

    Dim form2 as New Form2()

    form2.Show()

    C#:

    Form2 form2 = new Form2();

    form2.Show()

    To pass info into Form2, you could overload the Show method

    form2.Show(parameter for PK)

    or provide another method

    form2.LoadCustomerById(idOfCustomer)

    You might want to do some reading on Windows Forms for desktop or device as this model is common to both.

    Steve

  120. David Murillo says:

    Hi Steve

    I need to know if my client needs a type of CAL for an application that use a Mobile stand alone data base instaled in a device.

    I’m using SQL Server CE

  121. Steve.Lasker says:

    Hi David,

    SQL Server Compact is free to use, free to distribute, and free to embed within applications, so no, you don’t need any CAL’s to use SQLce.

    Connecting to SQL Server, whether through ADO.NET, Sync Services or Merge Replication requires a CAL for each connection, but that’s unrelated to the source that happens to be SQLce.

    The thing to not is SQLce doesn’t give people a free CAL to SQL Server.

    Steve

  122. Gilad says:

    If the Compact edition will throw an exception when trying to use it from a process hosted by IIS I can state that I will look for a different databse solution for my product.

    Gilad.

  123. Steve.Lasker says:

    Hi Gilad,

    What we’re trying to do is simply let developers know that SQL Server Compact, like other products in the same space, simply don’t compare with SQL Server.  We do support you ‘unblocking’ it for ASP.NET usage, but wanted to put a "sign" up so developers knew.  I’d suggest that other products in this space are no better, and in most cases worse for these scenarios under any sort of load.  

    For how to unblock, and the reasoning, here’s a post:  http://blogs.msdn.com/stevelasker/archive/2006/11/27/sql-server-compact-edition-under-asp-net-and-iis.aspx

    You can also simply check the SqlCeConnection docs, and you’ll see how the official documentation we placed in the product.

    Steve

  124. Heine says:

    Is it possible to program SQLCE from VB.Net Express 2005 or Express 2008? When I do "add new data source" I don’t see the SQLCE provider? I only see SQL Server and Access providers? I am able to create a SQLCE database from SQL Server Express Management Studio. I want to use the dot net 2 framework. So that’s version 3.1 right?

    I also tried using it from vb6 but I don’t see the oledb provider. I got the version of SQLCE that came with SQL Server Express 2005 sp2, but I also downloaded version 2005 (3.1) and installed it.

  125. Steve.Lasker says:

    Hi Heine,

    Unfortunately, we don’t have support in VS 2005 Express as Compact was released for desktop usage after 2005 shipped, and 2005 shipped SQL Server Mobile for the Windows Mobile platform which didn’t ship in any Express SKUs. I discussed the 2005 configuration steps here. However, you can use Compact 3.1 or 3.5 from VS 2005 Express in coding scenarios by simply adding a reference to the managed provider and partying away in VB.net or C#.  It’s really a tools limitation in 2005.

    In 2008, we have full support for Compact 3.5 across all VS and SQL Server SKUs, including Express.  The one exception is the VS 2008 Web Express SKU as we don’t yet have Compact enabled for web development.

    For VB6, while Compact does have an OleDB interface, it’s really targeted at the subset required for Windows Mobile.  It’s not a “full OleDb provider” required for desktop designer scenarios which is why you don’t see it as an option in any of the tools.  We have discussed expanding our OleDB support, but with our forward progress in .NET, we felt it best to look forward and do that much more in .NET.  That said, you could write a .NET assembly doing your data access, can enable that assembly for COM consumption.

    Steve

  126. Heine says:

    Is it possible to use SQLCE 3.5 with the dot net 2 framework? Alternatively can you use SQLCE 3.1 from VB Express 2008? I coudn’t add a new data source. I would like to visuallly define connections etc but I must use VB Express 2005/2008 with the dot net 2 framework.

    Is my only option to make a referance to the DLL?

  127. Heine says:

    Hi

    I downloaded the latest version 3.5 SQLCE and also the books online. I went thru the examples.

    It looks like when you want to use SQLCE for desktop apps and program it with managed code then you have to set a reference to the SQLCE namespace. It looks like you can’t use ado.net.

    I am using VB Express 2008. Is this correct?

  128. Heine says:

    I read a couple of things in the SQLCE books online. Apparantly you can perform a shrink operation on the database and you can also set an option to auto shrink. There is also an option to compact the database. Do you HAVE to perform a shrink? Doesn’t it re-use deleted space like SQL Server Express or the paid versions of SQL Server? Does auto shrink slow down your database operations dramatically? Is the purpose of compacting the database to remove fragmentation? I know in Access you have to compact in order to remove deleted space. In both cases it rebuilds the database when compacting. Just out of interest, how long does it take to compact a 4 gb database? Does SQLCE have any advantages over JET 4.0?

    (Access 2003) I know that JET 4.0 got a very small memory footprint but there are a number of DLL’s to deploy. It’s not as easy as SQLCE for deployment. Also, you need to buy Office in order to create/edit databases. Access 2003 is also limited to 2GB vs SQLCE’s 4GB database limit. I know SQLCE 3.5 needs 256MB RAM minimum, but what is the recommended memory requirements for SQLCE? SQL Server 2008 Express, which is still in beta, also has 256MB minimum memory, but the recommended memory is 1gb.

  129. Heine says:

    I read on another blog that you can install the design tools for SQLCE manually. I grabbed my Visual Studio Express DVD and did exactly that.

    I was able to go add new data source and I was able to view data in a simple form. I am sorry to bother you, but would you still consider answering my other questions? Yes you are right, you can only use the dot net 3.5 framework. I tried older versions of the dot net framework and got lots of compile errors.

  130. Steve.Lasker says:

    SQL Server Compact is based on a dependency of .NET FX 2.0. .NET FX 3.0 and 3.5 are really 2.0 +, +.  This is what happens when marketing and branding get involved in code versioning… 

    Due to the way VS 2005 and SQL Server Mobile 3.0 were packaged, we couldn’t achieve VS 2005 Express designers, so in 2005, yes, you’ll need to work in code.

    However, during 3.5, we did the work to enable Express designer support, so in VS 2008 you can use the designers across all the SKUs.  …but only for SQL Server Compact 3.5.  We weren’t able to add multi-version tooling support in this release.

    Steve

  131. Steve.Lasker says:

    Shrink is only required if you want to force the shrink operation immediately.  You can sort of think  of it like Garbage Collection.  It will kick in automatically, but sometimes you want to say “now”.

    Similar to GC, it doesn’t slow things down as it’s fairly efficient on when to perform compaction, so it’s typically best to let it do it’s thing.  If you’ve done a lot of deletes and you need to recapture the space immediately, you may need to call Shrink.

    There are many advantages over Jet, and some disadvantages.  With Compact, you can privately deploy the database engine directly with the app.  Jet is currently in many operation systems already, but is it the version you need?  Compact also has much better tooling in VS and SQL Server as this is the database DevDiv and SQL are focused on.  That said, Compact doesn’t have direct tooling support in Office yet as we’re not actively pursuing OleDB yet, and Office is just starting to “embrace” managed code.  

    I’m not sure where the 256mb min RAM came from, as it’s not really a Compact requirement, but rather an overall Windows and .NET FX requirement.  Compact consumes only a few megs, based on the size of the database.  Based on Windows Mobile requirements, you should never be limited in memory by Compact.  Rather WPF, WCF, .NET FX, etc.

    Steve

  132. Heine says:

    How do you browse a SQL Server Compact 3.5 database without installing Visual Studio 2008?

    Say if you got a database on a customer’s pc and you want to browse the data or execute queries for the purpose of troubleshooting?

    Something like query analyzer or SQL Server Management Studio? Or do you have to copy the

    customer’s database onto your laptop and browser it there from Visual Studio?

    Heine

  133. Steve.Lasker says:

    Hi Heine,

    Our tools for Compact 3.5 are composed into Visual Studio 2008 and SQL Server Management Studio 2008.  The good news is we now have full support in the Express SKUs of VS and Management Studio, but they are “installs”.

    For stand alone tools, or even tools that could be run directly from a DVD, you could expand upon this sample I’ve provided here, the database viewer.  It simply displays data, but you could expand upon it to show a graphical showplan with WPF, or other neat things.  There are several MVPs that have tooling external to VS, but they do need to be “installed” as well.

    Steve

  134. Heine says:

    Hi

    What do you think are the chances that SQLCE will be bundled with the next version of Access? My real question is if it will be possible to develop a front end with the next version of Access and use SQLCE as the back end? It should be possible in theory, but I don’t know if Microsoft would want to go that route.

  135. Steve.Lasker says:

    Hi Heine,

    It’s hard to say “Microsoft would want to go that route”, as Microsoft is such a big company made up of so many product units and teams.  As for packaging with Access, it’s not currently on our plan.  Because Compact is used in so many scenarios and products, it’s difficult to “pursue” each one individually.  We’ve been working to release Compact as an independent product that other teams can “pick up”.  While not directly linked to Access we will have future Office usage, but you’ll have to wait for PDC for that 🙂

    Steve

  136. Anthony Wieser says:

    Is there a front end for editing the contents of an SQL CE 3.5 databases anywhere?

    I’m developing with VS2005, but sometimes just need a quick and dirty way to display and edit fields in the database.

    Is there any simple way to do this?  Access 2007 doesn’t know how to attach an OLEDB table, and SQL Server Management Studio 2008 can display the results of a query, but doesn’t seem to allow you to make any changes to the data?

    Is it easy to knock up a simple .NET app that just displays a grid with the fields you can edit for example?  I’ve tried connecting using Excel 2007, and keep getting a message that the table doesn’t exist after going through the wizard.

    Anthony Wieser

    Wieser Software Ltd

  137. Heine says:

    Is there a way that you can download and install SQL Server 2008 Management Studio Basic on its own? I know it comes with SQL Server 2008 Express but I just want Management Studio Basic to browse SQLCE databases. Do you know what the minimum requirements are for Management Studio Basic? Some times you don’t want to install everything just the management studio basic.

    Heine

  138. Steve.Lasker says:

    Hi Heine,

    While we hoped to have a Management Studio Tools stand alone release, the Express team did fall back to only supporting a download that included the Express engine as well.  

    You can use VS 2008 Express (VB/C#) as small tool downloads to manage Compact databases as well.  Will that give you what you need?  Or do you need the Query Editor and Graphical Showplan features that currently only  ship in Management Studio?

    Steve

  139. Heine says:

    Hi

    I wanted a light weight product that would be able to browse and edit SQLCE databases. One that would be able to run on an XP machine with only 128MB RAM. I suppose it’s not too dificult to write your own tool. I don’t mind typing queries manually as long as I can see the results of those quries.

  140. Heine says:

    Steve,

    I also posted the same question in the SQL server forum and they said that during the SQL Express installation, you can choose to only install management studio basic. That sounds like the answer I was hoping to hear. I have already downloaded SQL 2008 Express, but I just wanted to know if it is possible to only install the managment studio basic.

  141. Not much today. WCF/SOA/WSE The WCF RSS Toolkit is a Windows Communication Foundation-based framework for generating RSS 2.0 and Atom 1.0 content feeds Steve Maine on the WCF RSS Toolkit The latest WCF Documentation CTP (June 2 release) is now available

  142. William says:

    Hi,

    I realise that this is a bit of an ask, but I’m hoping that you can help. I’ve been scratching my head for a while over various versions of this code snippet:

    string constr = "Data Source=\storage card\People.sdf";

    SqlCeConnection conn = new SqlCeConnection(constr);

    conn.Open();

    SqlCeTransaction t = conn.BeginTransaction();

    SqlCeCommand cmd = new SqlCeCommand("INSERT INTO People VALUES(@name, @age)", conn);

    cmd.Parameters.AddWithValue("@name","William");

    cmd.Parameters.AddWithValue("@age","4");

    cmd.ExecuteNonQuery();

    // here, I can read back the data with a SELECT and confirm

    // that a new row has been created in the database…

    t.Commit();

    conn.Close();

    Basically, the issue is that once I close the program the newly inserted row vanishes from the database. I can verify this using ‘Query Analyzer 3.5’ on the device… any ideas?

  143. John says:

    hi experts. i want to know few things as i'm trying my hands in vb.net application development.

    1) What is a bootstrapper, how does it help us?

    2) I've built an vb.net(Visual Studio 2008) application and now i want to deploy it in                  

    client's machines. I've used SQL server 2005 CE as backend and MS-Access 2003 for printing report(somehow logically).Now i want to incorporate both sql server compact edition and MS Access 2003 in the setup file i.e: when i install my project/software into client's system, it will first install full SQL server and MS Access if they are already not installed, and then my application. how do i do that?

    If possible Please send Your replies to my mail Id- "jumba_4u@yahoo.com". Please experts, let me know in detail if possible, thank u in advance, God bless u all.