SQL Server Compact Edition is advertised as a small embedded database that can easily be included with a Windows Forms application during deployment. According to some of the informal documentation online, one of the ways in which this is suppossed to be possible is to include the SQLce DLLs in the application file of your deployment. However, the official documentation (SQl Server CE Books Online) only mentions the options of installing the product via .msi on the client machine, or using a Click-Once application.
I would much rather deploy without having to do Click-Once or a full-install of SQLce. So I tried copying in the DLLs from the C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\ on my computer into the application directory during deployment, and it worked (!). There are 8 DLLs included, and through trial-and-error I was able to determine that (at least in my case) only four DLLs were really needed when deploying:
So I am in the middle of building a pretty substantial Windows Forms application in .Net 2.0/C#, which will rely very heavily on database integration. Currently development is proceeding using Sql Server Express 2005 for the database server. It is a very robust product that supports anything that you can do on the full edition of SQL Server, and is freely distributable. Stored procedures, triggers, the works.
However, when making the first internal release intended to be installed by other people, I ran into some problems, specifically related to the way that SQL Express works. As it is really the same thing as the full version of SQL Server (with limits put on DB size and other licensing issues), it requires a big download (around 35mb), large db file footprint (around 20mb) separate installation, and runs as a service on the client machine. Though the install can be packaged as part of your overall distribution and set to install “silently”, it is still an issue that requires ongoing support and trouble-shooting. Additionally, you can sometimes run into problems with the SQL Express service, permissions, problems attaching the database to the server, etc. It is a robust solution, but the potential problems involved in installing SQL Express on thousands of client machines and getting it to run flawlessly all the time is enough to give me pause.
When I was installing programs on my new laptop (Dell Latitude D820), some of my coworkers recommended that instead of installing Enterprise Manager (the client tools for SQL Server 2000), I should use the Microsoft SQL Server Management Studio that is included with SQL Server 2005 instead. It can perform all of the functions that Enterprise Manager/Query Analyzer can perform, except better. It is just constructed better, and lacks many of the annoying features of the previousl client tools (like how the modify Stored Procedure dialog box was modal, preventing you from doing anything else – like opening up another sproc and copying some text – while it was open, as well as close integration between the query windows and the manager interface). Plus, it could manage SQL Server 2000 databases perfectly well (which is important, as my company still uses this for their primary DB server).
So I installed the Management Studio a few weeks ago. Yet today I find myself reinstalling Enterprise Manager/Query Analyzer, because it has some features I need that Management Studio does not provide. Here they are:
- Cannot handle diagrams from Sql Server 2000 – This one was the deal-breaker for me. The database diagramming component of Enterprise Manager was always my primary way to define relationships and foreign keys between tables in the database. The graphical representation made the whole process much easier and helped me make sure that I wasn’t missing anything. When I tried to make a DB diagram for my 2000 database using Management Studio, I was directed to a MSDN help page that informed me that this was not supported by Management Studio (since MSSQL2000 uses a different diagram definition than 2005). Argh. I know that the format is different, but knowing that lots of people still use 2000, would it have been so difficult to add in support for the old formatting model? (This was the most annoying. The rest of the list are other pet peaces I have with Management Studio that I will be happy to see again when using Enterprise Manager)
- Setting the Identity Column – In Enterprise Manager, when defining the Identity column in a table, I can simply double click on the No in the Columns property display and it is set. In Management Studio, I have to First expand the Identity Specification section in the Column Properties, and then double-click on the (Is Identity) value. This extra click is annoying, especially considering how the Column Properties always needs to be made taller (it opens up too short) and the checkbox to expand the section is very small. Why can’t I just double-click on the value of Identity Specification like I do in Enterprise Manager and be done with it?
- Declaring Column Sizes – In Enterprise Manager, when defining a table, the column type and the size of the column were in two different rows, so when I want to enter a Varchar(100) row, I type “varc” (at which point the drop-down box identifies my selection as a varchar) “[tab]” (to get to the size column) “100″ to set the size. In Management Studio however, there is not separate column for column size. So when I am setting a Varchar(100) column, I have to type it all out “varchar(100)”. You might say “It is only five more characters, quit crying about it”, but it actually makes a difference in terms of work-flow (especially considering how if you want to change the size of a column that was previously set, you have to either retype the entire type+size, or use your mouse to highlight the numbers and change them. It is impossible to do the editing with the keyboard alone. Plus, if you make a typo when putting in the parentheses, you have to start over)
It is the small things that make the difference. So at least for now, I will be using Enterprise Manager again for initial database setups. Too bad, since other than these things, Management Studio really does excel. (Subtle hint from anyone in the Microsoft SQL Server 2005 Management Studio team who is reading this).
Things that I found interesting on July 20, 2006: