SQL Desktop Engine

Question

Does anyone have experience using the MSDE (Microsoft SQL Desktop Engine) for desktop apps? I'm wanting to use this to store my data locally, sync up with a SQL2K server, and keep the same native .Net SQL interfaces rather than using ODBC classes. So I'm curious to hear if anyone has worked with this, and what you thought about it, especially the replication portion if it. thanks, -James PS: I know this post belongs in the ADO.NET forum, but since there are zero posts in there, I figured I'd have a better chance of a response here) :)

dip_2004_05 · Answer

I am sure you must already be aware with it, but quoting MSDN : "Because MSDE is based on the same data engine as SQL Server, most Microsoft Access projects or client/server applications run on either version unchanged. However unlike SQL Server 7.0, MSDE has a 2 gigabyte database size limit, doesn't support Symmetrical Multiprocessing (SMP) on Windows 95 or later, and when using Transactional replication, can't be a replication publisher (although it can act as a replication subscriber)." Overall, MS claims MSDE is pretty much the same as SQL Server, except for EM and the size/concurrency limitions.. Theoretically Replication with SQL2k should work as expected and I haven't heard otherwise ;) Another extract from MSDN: "Q: Can MSDE participate in replication with a central SQL Server? A: Yes. MSDE can participate in most types of replication with either a centralized SQL Server or another MSDE. Specifically, MSDE can participate fully in snapshot and merge replication, but only as a subscriber in transactional replication (MSDE cannot be a publisher in transactional replication.) For a full explanation of these types of replication, see the Overview of Replication in the SQL Server programmers guide. Also note that a per-set Client-Access License (CAL) is required if MSDE is participating in any type of interaction with a SQL Server, including replication. A CAL is not required if MSDE is replicating with another MSDE. Q: Can MSDE databases participate in Multi-Server, Multi-Transaction operations? A: No. Multiple transaction processes across servers cannot include any machines running MSDE. These advanced operations cannot involve any MSDE databases. MSDE databases cannot request services from a SQL Server without a per seat SQL Server CAL (Client Access License). Check the SQL Server site for more information on SQL Server licensing." Sorry - no practical insights here - just 'hearsay'... Hope this helps - dip