Following are the questions and answers from my chalk talk session on SQL Server 2005 Express (hereinafter referred to as SSX) at Code Camp II. Note that I’ve rephrased some of the questions to better suit this format. Many thanks to Aaron Weiker of http://aaronweiker.com/ for capturing these notes during the session.
| Q. |
Is SSX free? |
| A. | It’s free in the sense of "Free Beer" in that Microsoft allows you to use whatever Internet connection you can to download it without imposing a charge on you. Microsoft doesn’t charge you any fees to using the product, nor do they charge you any fees if you redistribute it as part of any product you create. It’s not "free" in the GPL or other "open source" software products. |
| Q. |
Where’s the GUI? What’s Express Manager? What’s QARA? |
| A. | At this time, a GUI-based program specifically designed for working with SSX hasn’t been released to the community. Microsoft has shown previews of a program called "Express Manager" (XM) that will eventually fill that role. Microsoft has said that XM is planned to be included with the Beta Three release. In the mean time, Lloyd Sheen crafted a program called QARA (Query Analyzer Replacement Application) that provides a great way to work with SSX. Note, however, that QARA can only be installed on computers running the June 2004 release of the .NET Framework version 2.0. It can be co-located on a computer with the October 2004 Community Technical Preview release of SSX. |
| Q. |
What Operating Systems are supported? |
| A. | SSX can be installed on all Windows 2000 versions, Windows XP Professional (XP Home is not supported since it lacks the ability to participate in a domain) and all editions of Windows Server 2003 that support version 2.0 of the .NET framework. |
| Q. |
Are you a Star Trek fan? |
| A. | yIDoghQo' (if you don’t speak Klingon, http://www.kli.org/tlh/phrases.html) |
| Q. |
What is CTP? |
| A. | It’s short for "Community Technical Preview." It’s a moniker Microsoft uses to describe releases make available to interested public between full beta releases. |
| Q. |
How do I get remote connections (that is connections to the database from another computer connected on the same network) to work? |
| A. | It’s a longer answer than I could accurately give, so I referred to this posting on the SSX blog: http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx. |
| Q. |
Is this designed for remote use? |
| A. | IMHO, no. While it can be used as a general purpose database if you’re willing to jump through a few hoops, that’s not really what’s been designed for. It serves well as in three cases: embedded in an application; supporting a web site with moderate traffic website and as a small workgroup database where you have a front-end tool like Access 2003 acting as the client. |
| Q. |
What are the limitations of SSX as compared to MSDE? |
| A. | Mostly that the "Workload Governor" was been removed, so that should help in the Website use scenario. SSX will also support databases up to 4GB, compared to 2GB in MSDE. However, SSX will only use the first CPU (CPU 0) in the machine and, at most, the first Gigabyte of memory. |
| Q. |
How does SSX participate in Replication? |
| A. | SSX can be subscriber in many replication scenarios, but it can not be a publisher. Until XM ships, you need to use a technology called RMO to create and manage subscriptions. |
| Q. |
How about managing backups? |
| A. | XM may offer tools for automating them more, but in the mean time, backups and other maintenance tasks are best dealt with using SMO. See Jasper Smith’s series on using SMO starting at http://www.sqldbatips.com/showarticle.asp?ID=27 |
| Q. |
How does the removal of XQueryProcessor from ADO.NET affect XQuery in SQL Server 2005 and SSX? |
| A. | It really doesn’t. XQuery is still going strong there. |
| Q. |
Is there a way to aggregate queries against multiple databases? |
| A. | There’s nothing new in SQL Server 2005 for that I’m aware of. |
| Q. |
What is SQL Server Management Studio? |
| A. | It’s the integrated replacement for Enterprise Manager, Query Analyzer and some other tools for SQL Server 2005. |
| Q. |
What development tools work with SQL Server 2005? |
| A. | Anything that supports ODBC, OLE-DB or the new "SQL Server Native Access Client" (SNAC) should work fine so long as you’ve configured the database for it. See the previous question about "remote access" for what’s need to make that work. Keep in mind, though, that Access version prior to version 2005 will probably be limited or problematic. |
| Q. |
The Beta and CTP versions expire in 360 days after install. Is this long enough to make to Release To Manufacturing? |
| A. | I’d hope so! Remember, we will likely see another beta release and probably one or two Release Candidates before the product goes "Gold" so you should be able to keep going until then. |
| Q. |
Is there another community preview in November for SQL Server 2005? |
| A. | Today, I wouldn’t expect another release until Beta 3 later this year or early next. |
| Q. |
Can you code against SQL Server 2005 with the current ADO.NET? |
| A. | Yes, see previous question. Where you can, I suggest using the new SQL Native Access Client though since it solves issues with distributing MDAC. |
| Q. |
Is SQL Service Broker included with SQL Server Express? |
| A. | SSX can participate in Brokered transactions, but cannot be used as Broker engine. |
| Q. |
Can SNAC and MDAC run side by side? |
| A. | Yes. |
| Q. |
Can I build a database using SQL Server 2005 and attach it to SSX? |
| A. | Yes, the database formats are the same. |
| Q. |
Can I build a database using SQL Server 2000 and attach it to SSX? |
| A. | Yes, as long as you are running in SQL Server 2000 compatibility mode. |
| Q. |
Can SQL Server Express 2005 be managed easier than MSDE? |
| A. | Depends on the management tool being used (XM, SSMS) and if the Express instance is running. By default, SSX doesn’t start by default and many of the usual networking protocols are disabled. |
| Q. |
What is an SKU? |
| A. | In general, it is an acronym for Stock Keeping Unit. Microsoft uses the term to refer the various editions of a product like SQL Server 2005 Developer, Standard, Enterprise and Express. |
| Q. |
What is the relationship with SQL Server CE? |
| A. | There will be new version of SQL Server for compact devices that uses the .NET compact framework. The new version is called SQL Server Mobile Edition (SSM). You can get more information about that here: http://www.microsoft.com/sql/ce/mobility/beta1.asp |
| Q. |
What devices will SSM support? |
| A. | Initially devices running version 2.0 of the .NET compact framework as I understand it. Future releases are planned for the SmartPhone and TabletPC. |
| Q. |
What do you know about the enhancements for SQL Mobile? |
| A. | Not much. (grin) |
| Q. |
What about hotfixes and service packs for SSX? |
| A. | Its my current understanding that unlike MSDE, the service pack releases for SSX and other SQL Server 2005 SKUs will at least concurrent if not unified. |
| Q. |
What does the CLR Integration mean? |
| A. | By CLR, We mean the .NET Common Language Runtime. This allows .NET assemblies to be written/compiled and then run inside of SQL Server. |
| Q. |
What is a Stored Procedure? |
| A. | It is a pre-compiled set of statements. Micro-programs, if you will. |
| Q. |
Is it good to put business logic into a .NET assembly? |
| A. | Well, if its one you plan to use as a CLR-based type, function or stored procedure, I’d say the general answer is no since the CLR hosting isn’t well suited to the kinds of resource commitments such objects would normally use. |
| Q. |
Does SQL Server 2005 have a different CLR than the rest of the .NET framework? |
| A. | Yes and no. It uses the same binary distribution of the Framework as, for example, ASP.NET 2.0 uses, but it is different in terms of it hosted. The primary difference is that in the SQL hosted version is managed (in terms of threads and memory allocations) by SQL Server. |
| Q. |
Does the SQL Server use the system GAC? |
| A. | No. Assemblies are registered at the database level. |
| Q. |
If I register an assembly in master, wouldn’t that make it visible in all databases? |
| A. | Yes and no. No, that assembly won’t available in the scope of the current database, but yes, you may be able to call it using a three-part name (master.schema.objectName) |
| Q. |
If I register an assembly in model, would new databases inherit the object? |
| A. | Yes. |
| Q. |
Can you package Express with your application? |
| A. | I’m sure that possible, but it might be more efficient to redistribute the same install that Microsoft offers from the SQL Express web site for troubleshooting purposes. You can include your MDF files, plus any setup, programs or data population scripts. |
| Q. |
Does SQL Express cause problems with MSDE? |
| A. | Not that I’ve experienced so far. |
| Q. |
Why is XP Home not supported? |
| A. | XP Home lacks the system components needed to enable Windows Integrated Security work. Since this is the default setting for SSX, you could end up with an unmanageable and unusable installation if you installed it on XP Home. |
| Q. |
How many total instances of SQL Server 2005 and SQL Server Express can be hosted on the same machine? |
| A. | 64. |
| Q. |
Is it legal to distribute the framework with your application? |
| A. | That’s a better question for an attorney, but as I read http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetdep/html/vsredistdeploy.asp, I believe the answer is yes. |
| Q. |
Can I use Express for Windows Sharepoint Services? |
| A. | Not tried it myself, but I can’t think of any reason why it wouldn’t. I do know that Windows SharePoint Services seems to work with SQL Server 2005 Beta 2. |
| Q. |
Can SQL Server 2000 tools like Enterprise Manager be used to manage an SSX instance? |
| A. | No. SQL Server 2005 and its tools are based on a newer data management object model called Server Management Objects. SQL Server 2000 is based on Data Management Objects. Although SMO may allow the management of DMO based, its not going work the other way around. |