Transcript SQL Express

SQL Server Express
Sydney 2005
November 30
Scott Baldwin
Associate Consultant - Readify
Prerequisites for presentation:
I assume you know:
1) .Net Development
2) Database Experience
Level: Intermediate
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
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
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.
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
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
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)
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
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
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)
User Instances

RANU
– Run As Normal User
– Designed for JET scenarios
– Better isolation and security
– Works for LUA User
User Instances cont…

How it works
User App
1.
Connect to Parent Instance w/
UserInstance = TRUE
LUA User
SQL Server Parent Instance
3. ADO.NET redirects
connection to UI
2. Parent Instance spawns
User Instance
User Instance
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
Demonstration

Demo of RANU and XCopy
Target Users



Hobbyists & non-professional
Technology Professional
ISVs – Shrink wrapped software market
Usage Scenarios




Software vendor creating different SKU’s can
implement lowest SKU with SQL Express.
Trial products.
Shrink wrapped single user software.
Smart Client (sometimes connected
scenarios)
Resources

http://lab.msdn.microsoft.com/express/sql/
http://blogs.msdn.com/sqlexpress/
http://www.sqlserver.org.au/
http://www.sqldownunder.com/
[email protected]

http://sjbdeveloper.blogspot.com




Discussion

Questions