SQL Server Express

1
SQL Server Express
Scott Baldwin Associate Consultant -
Readify Prerequisites for presentation I
assume you know 1) .Net Development 2)
Database ExperienceLevel Intermediate
Sydney 2005 November 30
2
SQL Server Express Session Overview

• What is SQL Server Express (SSE)
• Feature Overview
• Comparison with MSDE 2000
• Visual Studio Integration
• XCopy RANU
• Demo
• Who Will Use It?
• Usage Scenarios
• Resources

3
What is SQL Express?

• Based on SQL Server 2005 Database Engine
• Complete SQL Server Programming Model
• Transact-SQL
• Stored Procedures
• Views
• Triggers
• Cursors
• New for SQL Sever 2005
• CLR Integration
• XML
• Enhanced Security

4
What is SQL Express? Cont

• Replacement for MSDE 2000
• Freely Downloadable Redistributable
• Integrated setup and deployment
• Tight Visual Studio Integration
• Native and Managed Data Access
• Automated patching and servicing
• Some restrictions placed on database size,
  performance and feature set.

5
Feature Comparison
SQL Express MSDE 2000
Database size 4GB 2GB
Number of CPUs 1 CPU 2 CPUs
Buffer RAM 1 GB 2 GB
Workload Throttle No After 8 concurrent workloads (DBCC CONCURRENCY VIOLATION)
GUI Tools SQL Server Management Studio Express Edition None
Visual Studio Integration Deep Integration Basic Integration
6
Feature Comparison cont
SQL Express MSDE 2000
DTS No Runtime present
Deployment MSI Only MSI merge modules
Replication Subscription for Transactional Merge Merge publish subscribe, No Transactional
SQL Agent No Agent Agent present
Supported OS Supports Win 2000 SP4, XP Sp1, and Win 2003 Supports Win98, Win Me, Win 2000, XP, NT4, Win 2003
7
Other Restrictions

• Clustering
• Mirroring Log Shipping
• Database Snapshot
• Online Operations (Online Index Rebuilds)
• New backup-restore features
• Plan Guides
• SQL Agent
• Analysis Services
• SSIS (SQL Server Integration Services)

8
Other Features

• Other SQL Server Features
• Service Broker (to/from another SKU of SQL
  Server)
• SQL Express only
• User Instances (RANU / XCopy Deployment)
• Post RTM (Autumn 2006)
• SQL Server Management Studio Express Edition
• Reporting Services
• Full Text Search

9
SQL Server Express Deployment

• No Merge Modules
• MSDE was hard to service if distributed as a
  Merge Module (Slammer).
• Issues with programs installing MSDE multiple
  times on same machine
• MSI installer only, with both interactive/silent
  modes
• Much better maintenance story
• Highly customisable

10
XCopy Deployment

• XCopy Deployment
• Treat the database as a file
• Use AttachDBFileName and DataDirectory
• Simplifies deployment
• Must be an Admin on the instance to attach
• Lose some functionality (eg Replication)

11
User Instances

• RANU
• Run As Normal User
• Designed for JET scenarios
• Better isolation and security
• Works for LUA User

12
User Instances cont

• How it works

1
.
Connect to Parent Instance w
/
UserInstance

TRUE
3
.
ADO
.
NET redirects
connection to UI
2
.
Parent Instance spawns
User Instance
13
User Instances Cont

• Limitations
• Supports only Windows Authentication
• Listens only on local named pipes
• Replication is not possible
• Administration
• Only members of the Administrators group, and the
  spawning user can connect
• Exposed through sys.dm_os_child_instances

14
Demonstration

• Demo of RANU and XCopy

15
Target Users

• Hobbyists non-professional
• Technology Professional
• ISVs Shrink wrapped software market

16
Usage Scenarios

• Software vendor creating different SKUs can
  implement lowest SKU with SQL Express.
• Trial products.
• Shrink wrapped single user software.
• Smart Client (sometimes connected scenarios)

17
Resources

• http//lab.msdn.microsoft.com/express/sql/
• http//blogs.msdn.com/sqlexpress/
• http//www.sqlserver.org.au/
• http//www.sqldownunder.com/
• sqldownunder_at_listserver.readify.net
• http//sjbdeveloper.blogspot.com

18
Discussion

• Questions