SQL Server Express Edition
When we started looking at what to do for “MSDE” in SQL Server 2005 we changed our approach and some of our fundamental assumptions. First of all we created a dedicated team derived mostly from the SQL Server Mobile team in Redmond (up until now MSDE had been a virtual/part time team). This team did an in-depth analysis of the overall space and also the desires of our partners in this space, mostly ISVs and Visual Studio.
One of the first decisions out of this group was that the Governor had to go. It was simply causing too much confusion, but we had to find a way to limit “MSDE 2005” hence the memory and proc limits came in. We upped the DB size limit from 2GB to 4 GB as that was just a reflection on how much data was being generated and was useful to customers.
Let me repeat for clarity THERE IS NO WORKLOAD GOVERNOR IN SQL SERVER EXPRESS EDITION.
Now some of the harder work started, first up was naming, MSDE had some brand recognition behind it, both good and bad, so we were torn on what to do here however in many ways this decision was made easy as we knew that the VS team was working on their low end solution and “MSDE 2005” was going to be a key component of this, once they decided on the name Express that seemed like a good plan to follow.
Next up was size/footprint, this presented a huge challenge, in case you had not noticed yet SQL Server 2005 is a vast product, there is more of everything, including disk space requirements. Based on user research the original goal for SQL Server Express was for it to be a 35MB download, well when we built the first version we had something of a shock coming, my memory is a little vague but I seem to remember the download being 150MB and it taking 400MB on disk! Thus started the SQL Express diet, led by Dave Nettleton we looked for everything that could be taken out of Express, we looked for optimizations, new compression algorithms, you name it we looked. It was not an easy task, in SQL Sever 2000 sqlserv.exe is a shade under 9MB, in SQL Server 2005 it’s a tad over 28MB. So we had to be very very aggressive, as part of this exercise SQL Agent and DTS were removed along with some other aspects of the product.
Over the course of a number of months we got very close to our goals for size and footprint and during that time expectations changed a little so we felt we succeeded.
The penultimate big change was in the GUI aspects, as MSDE was originally designed as ISV embedded store we did not provide a GUI. WOW did we get a lot of feedback on that topic…Hence we chose to reverse that decision and do something in the GUI space, but once again we looked at the problem from scratch so our options were basically;
1/ A Web based tool that would run locally under Cassini like Web Matrix and the sample Web Tool already did.
2/ Build a cut down Management Studio.
3/ Build a new tool from scratch (Express Manager).
We settled on 3 for a variety of reasons but the 2 key ones were size (feedback at the time was that this thing needed to be < 5MB download) and we could provide a more appropriate experience vs SSMS which is very much an Enterprise focused tool.
Eventually we shipped a CTP of XM, which for the most part was received ok, but I feel got a bad rap. That initial CTP did everything we wanted it to do; we showed the new concept dialog (compare the number of options on the SSMS Create DB Dialog with the 3 on the XM Create Database Dialog), we had prioritized a query editor so that folks could do “everything” using the fall back of script. However lots of folks thought this was all that XM was going to be and based on that and other factors, the team switched plans to deliver SSMS-E (SQL Server Management Studio – Express Edition) instead.
Having done all this only one really large piece of feedback remained unaddressed and that was the aspect of JET that kept deployment very simple, the single file that could be located anywhere. This presented quite a challenge as a file based DB and a service based DB are VERY different, however the team found a solution in what we call User Instances. These are worthy of several blogs all by themselves hence I’ll not repeat the content here.
All of the above brings us to SQL Server 2005 RTM/Today, but the team has not stopped listening and thinking about this space, watch out for Express Advanced Services in the coming weeks, more starter apps and more info on express.
In MSDE for SQL Server 7/2000 the DTS runtime was included, this included the Package and Pump object models as well as the dtsrun tool, but there was no wizard and no designer. This decision goes back to the target for MSDE being ISVs, the idea being they could use DTS as an embedded component of their app(ne of the other reasons for none of the UI elements being included(and we did spend a lot of time looking into shipping the designer as a control and shipping the wizard) was actually cost, MSDE was localised into more languages than the rest of SQL Server because it was included in Office and the cost of doing those extra languages was VERY high in terms of test time etc as we had to special case everything and not use all the std infrastructure we had for the other languages.
So onto SQL Express, SQl Express does not include DTS or its replacement SSIS, we looked at just shipping the DTS2000 runtime but there were all sorts of challenges with that, we also looked into shipping the wizard, but that added a lot of extra space to Express, we sweated blood to try and keep the size down, hence SSIS was not included, although I know the team are looking at this decision again based on forum feedback.
Ok there is no DTS and no SSIS, so why did this page state that import/export is a feature of express? Well there are more ways to get data into and out of SQL Server and in fact its one of those generational things, if you were brought up on SQL 7 or later than you likely think of DTS/SSIS for import export, if you were brought up a little earlier than that then you probably think of BCP and OpenRowset, more about these further down. We did actually go back and forth a fair amount on the descriptions used in the matrix for the SKUs as to whether we should talk about import export, in the end we decided to do it but it looks like we created some confusion, so the page has been flipped back.
Lets talk about different types of data;
Text
If you want to import text there are several options,
- BCP is a command line tool that can be used to import text files, it runs as an external process but has been around for ages and is stable and predictable, it also has some nice touches like format files.
- Bulk Insert is a T-SQL statement that runs inside the SQL Server process space(so there are perf advantages, it handles much the same as BCP and also supports format files including a new easier to read XML format in SQL2005, however its future is less certain.
- OpenRowset, this is the new recomended T-SQL way, I have not played with it much but it seems fast, stable and easy to use.
- Code, you can always use the text file classes in NetFx (especially those that come with the VB package) and then just use the SqlBulkCopy object(this will accept a DataSet a DataTable or a DataReader).
XML
Again several options,
- OpenRowset, work just as easily with XML as it does with Text
- Code(1) you can use System.Xml and System.Data to generate a dataset, datatable etc, there is actually some new capabilities in .Net 2.0 around the datatable being able to consume XML directly
- Code(2) XMLBulkLoad is a very cool COM object that allows you to load mutliple tables from a single XML document (under the covers it uses OLE DB and FastLoad which is what DTS and SSIS use).
- Exotic... couldn't think of a better word, you can use other products like Biztalk to get XML data into SQL Server
Excel/Access
- OpenRowset using the Jet OLE DB Provider
- Access includes a capability to copy data into SQL Server, earlier versions will not work with SQL Server 2005
Other Relational Sources
- OpenRowset with the right OLE DB Provider.
Solutions
Micosoft has given the solution in Microsoft SQL Server 2005 Express Edition Toolkit. Use this after install the toolkit "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe"