SqlCeException on application's first use of SQL CE

We discussed how SQL CE get's into trouble of failing to load SQL CE DLLs in an earlier blog article "Can't load SQLCE DLL".  As you can see this occurs when SQL CE is trying to load the native engine.  Note that, SQL CE delay loads the DLLs and hence the trouble.  Hence, we suggested in the earlier blog article to pre-load all required SQL CE DLLs so that you give chance to load SQL CE DLLs when there is memory. 

This issue shows up on the following instances:

1) Application is trying to fill the datagrid using SqlCeDataAdapter.Fill (or DbDataAdapter.Fill)

2) Application is trying to do merge replication with the backend server using SqlCeReplication.Synchronize

3) Application is trying to perform RDA operations with the backedn server using SqlCeRemoteDataAccess.Pull, SqlCeRemoteDataAccess.Push, or SqlCeRemoteDataAccess.SubmitSQL

With that context, and coming to what is different from our earlier versions is being discussed in this article.  As said in the above articles, application developers are requested to pre-load SQL CE DLL.  If you have chosen to go with pre-load approach and did a LoadLibrary("sqlcese30.dll") earlier, then it requires a little change.

SQL CE v3.0 released before we moved onto desktop.  Once we moved to desktop (in addition to device) with SQL CE v3.1 release, lot of customers started developing applications that work both on device and desktop.  One of the differences our customers came back was the installation model.  On desktops, we install under %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.1 and on device we install under \Windows\.  We worked on that feedback and unified our installation in SQL CE v3.5.  That is, with SQL CE v3.5 onwards you can see that we always install under Program Files irrespective of the platform.  However, at the same time we did not want our customers to hardcode the installation folders.  So, we unified our registry keys too.  With SQL CE v3.5 SP1, you can use the registry key "HKLM\Software\Microsoft\Microsoft SQL Server Compact Edition\v3.5\InstallDir" to find the install location. 

Now coming back to troubleshooting, earlier Load Library calls with out path qualification worked on devices as we were under \Windows and hence the path probe was successful.  Now with our unification, we moved to a non-standard path and hence path probe would have failed and so was your LoadLibrary.  Hence, we request application developers to update their code to read registry and then use Load Library with full path so that you never worry about our changing install locations.  We strongly recommend you to use the registry to find the installation location as sometimes we may need to be under (\Windows) especially when going IN-ROM hence registry is always realiable model than hardcoded installation path.  I agree that you are hardcoding registry path than directory path, but we guaranty that registry path remains static for a version,  but not installation folder.

Thanks,

Laxmi Narsimha Rao Oruganti

గణపతి స్తోత్రములు

చాలా రోజుల తర్వాత మళ్లీ  ఒక తెలుగు బ్లాగు వ్రాయాలని కోరిక కలిగింది... వినాయక చవితి పండుగ కూడానూ! అందుకే ఈ సంచిక

ఆగజానన పద్మార్కం గజానన మహర్నిశం | ఆనేక దంతం భక్తానాం ఏక దంతం ముపాస్మహే ||

అంతరాయ తిమిరోప శాంతయేత్  శాంత పావన మచిన్త్య వైభవమ్ | 

తం నరం వపుషి కుంజరం ముఖే  మన్మహే కిమపి తున్దిలం మహ: ||

వక్రతుండ మహాకాయ సూర్యకోటి సమప్రభ

అవిఘ్నం కురుమేదేవ సర్వ కార్యే షు సర్వదా

మంత్ర హీనం క్రియా హీనం భక్తి హీనం గణాధిప

యత్పూజితం మయాదేవ పరిపూర్ణం తదస్తుతే  

అనయాధ్యాన వాహనాది షోడషోపయాచార పూజయాచ భగవాన్ సర్వాత్మక :

శ్రీ మహాగణాధిపతి: సుప్రీతస్తు ప్రసన్న వరదో భవతు ఉత్తరే కర్మణ్య విఘ్నమస్తు

ఓం గజాననం భూత గణాది సేవితం  కపిత జంభు ఫలసార భక్షం |

ఉమాసుతం శోక వినాశకారణం నమామి విఘ్నేశ్వర పాద పంకజం ||

శుక్లాం భరధరం విష్ణుం శశివర్ణం చతుర్భుజం |

ప్రసన్న వదనం ధ్యాయేత్ సర్వ విఘ్నోప శాంతయేత్   ||

ఇట్లు మీ భవదీయుడు,

లక్ష్మీ నర్సింహారావు ఓరుగంటి

SQL CE v3.5 SP1 Features

Before we start, as usual the disclaimer holds J and plans may change. 

SQL CE v3.5 SP1 is getting released with SQL Server 2008 and Visual Studio 2008 SP1.

Q: Why blog about a service pack, it is just part of life cycle?

A: There are some goodies that are coming along with this service pack.  Hence, the blog post J

The goodies:

1)     Case Sensitive Databases

2)     Native 64-bit support

3)     Platform Builder Integration (Catalog Items)

Legend:

Case Sensitive – CS, Case Insensitive – CI,

Windows Desktop Native 64-bit engine – win64

Windows Desktop Native 32-bit engine – win32

Case Sensitive Databases (a.k.a. Case Sensitive Collations):

With SQL CE v3.5 SP1 onwards, we now support case-sensitive databases.  We have done things little different.  The metadata like database object names (i.e. table name, column names, etc) are always case insensitive irrespective of the sensitivity of the database.   The case sensitivity is applicable only to real user data.  Also note that the case sensitivity is always at database level (not table or column or query level). 

To create case sensitive database, add “Case Sensitive=true” to connection string.  If you are an OLEDB developer, please use property DBPROP_SSCE_DBCASESENSITIVE.

If you want to know the case sensitivity of the database already created, use SqlCeConnection.GetDatabaseInfo API.

Merge Replication

You can Merge Replicate SQL CE DB in all four combinations of case sensitivity with SQL Server.  That is,

SQL CE CI DB – SQL Server CI DB – Already supported

SQL CE CI DB – SQL Server CS DB – Already supported

SQL CE CS DB – SQL Server CI DB – New combination

SQL CE CS DB – SQL Server CS DB – New combination

However, in the cross combinations users have to be cognizant about the sensitivity mismatch and put the data in to the DB which is acceptable to both of the sync end-points in terms of constraints, indexes …etc.

ADO.NET Sync Services (aka: OCS)

This is not supported with case sensitivity.  Though you can use CS DB with sync services, you cannot really have the rows that differ only in case with sync services.  This is not tested and hence cannot guaranty.

Remote Data Access

RDA has been in maintenance mode for almost two versions and we don’t have any plans to make it work with new scenarios and so is not supported.  It might work, but officially we don’t support it.

Tools

Both SQL Server 2008 Management Studio and Visual Studio 2008 SP1 can be used with CS DB.  You can create SQL CE CS DB using these tools UI.

Compatibility FAQ

#1: Since, SQL CE v3.5 SP1 MSI always upgrades SQL CE v3.5 RTM MSI.  So, this is not a user scenario when using MSI installation.  However, if you have private deployments of SQL CE v3.5 RTM for your app and you are also using VS 2008 SP1 or SQL Server 2008 to operate on the database, then this is an important point to note and we recommend you to push SQL CE v3.5 SP1 bits in your private deployments to help the end user.  Also there is a good chance that the private deployment to start working with central deployment because of CLR assembly binding semantics.

Native 64-bit Support

With SQL CE v3.5 SP1, we will be providing native 64-bit support.  SQL CE v3.5 SP1 64-bit native engine would be available as a web download.  We don’t have a different tooling support for our native 64-bit.  Note that, you can still have a box with SSMS 2008 and our native 64-bit working on a same database.  You really do not need a 64-bit tooling as databases are compatible.

Platform Builder Integration

SQL CE v3.5 SP1 would be available as a catalog item in Platform Builder Versions 5.0, 6.0. 

Thanks for reading! 

Laxmi Narsimha Rao Oruganti

Harivarasanam

SQL Server Compact Database File Security

SQL Server Compact Database File Security

SQL Server Compact (SQL CE) supports password protecting the database file. 

1)      Password is a must to encrypt SQL CE database

2)      When you create a SQL CE database with a password, it automatically gets encrypted

Now there have been many questions around on about the strength of the security.  The strength of the database file security lies in the encryption and authentication algorithms used to protect it.  Please keep your question of why authentication algorithm is required and why not just encryption algorithm sufficient enough in your mind only and I hope you agree with me on the fact that some details better be not open for the sake of better securityJ.

Now coming to what exactly are the algorithms used in what version is outlined in a table below:

Connectivity Cookbook

Merge Replication:

1)   Making deployment easy by automatic new subscriber creation with a copied subscription.  See more.

2)   Avoid heavy handshake and empty synchronizations by detecting the pending changes on server and client.

3)   Get the best out-of SQL Server Database Mirroring and Failover.

4)   Know more and do better

a.     What is in the system?

b.     Verifying the system

Remote Data Access (RDA):

1)   This has to be as thin as RDA J, hence just FAQ

Common:

1)   Getting ready for enterprise, working with proxies

2)   Cross Version Compatibility (v3.1, v3.5)

3)   Troubleshooting the system

a.     Have you ever got annoyed with 28559, kill that!

A hearty welcome

It's been long time I have been blogging on our team blog.  I thought it is time for me to have my own blog and here comes one!

I am hoping that this blog would help community to find useful information.

Slokam:

వక్రతుండ మహాకాయ సూర్యకోటి సమాప్రభ 

నిర్విగ్నం కురుమేదేవ సర్వకార్యేషు సర్వదా||

Vakrathunda Mahakaaya Suryakoti Samaprabha

Nirvignam Kurumedeva Sarvakaryeshu Saravada ||

Meaning:

O elephant headed and large bodied Lord, radiant as a thousand Suns, I ask for your grace so that this task I am starting may complete without any hindrances.

Krithagnyathaabhivandanamulu,

లక్ష్మీ నరసింహా రావు ఓరుగంటి

Laxmi Narsimha Rao ORUGANTI