Queensland SQL Server Users Group 22nd June 2006

1
Queensland SQL Server Users Group22nd June 2006
SQL Server Express with Advanced Services

• Mark Morton
• Dimension Data Learning Solutions
• mark.morton_at_didata.com.au
• MCT, MCDBA, MCITP, etc, etc

2
Who Am I? Mark Morton MCT, MCDBA, MCTS(SQL 2005)
Technical Instructor / Consultant
Specializing in SQL Server, .NET Development,
Security, GO, Start Trek and Rugby
League mark.morton_at_didata.com.au
3

• Agenda
• SQL Server Express 2005 What is it?
• What you get
• What you dont get..
• Advanced Services Toolkit
• Installing SQL Server Express
• Pre-requisites
• Install Order
• Licensing!!!!
• SQL Server Express Tools
• Configuration Tools
• SQL Management Studio
• DEMO XCOPY Deployment, Full Text Indexing
• Business Intelligence Studio
• Reporting Services using SQL Server Express
• DEMO Design, Deploy and View report
• (Time Permitting) the competition Pentaho
• Summary / QA

4
Microsoft SQL Server Express Edition 2005
What IS IT ??
5

• SQL Server 2005 Express Edition
• SQL Server Edition Comparison
• SQL Server Express with Advanced Services

6
Introducing SQL Server 2005
Express
Workgroup
Standard
Enterprise
Complete data management analysis platform for
medium businesses and large departments
Fully integrated data management and analysis
platform for business critical enterprise
applications
Easiest to use most affordable database
solution for smaller departments growing
businesses
Fastest way for developers to learn, build
deploy simple data driven applications
4 CPU Unlimited RAM (64-bit) Database
Mirroring Enterprise ETL Simple OLAP
Server Simpler Reporting Full Replication SSB
Publishing
1 CPU 1 GB RAM 4GB DB Size Simple Management
Tool Report Wizard Report Controls Replication
SSB Client
2 CPU 4 GB RAM Management Studio Import/Export
Limited Replication Publishing Back-up
Log-shipping
Unlimited Scale Partitioning Adv. DB
mirroring, Complete online parallel operations,
DB snapshot Advanced Analysis Tools including
full OLAP Data Mining Customized High Scale
Reporting Report Builder Adv ETL - complex
transforms
3.9K per proc or 739 (Server 5 users)
6K per proc or 2,799 (Server 10 users)
25K per proc or 13.5K (Server 25 users)
Free
7
(No Transcript)
8
SQL Server Express At A Glance
9
SQL Express Engine

• Full SQL Server 2005 Database Engine
• CLR and XML support
• Full T-SQL w/ Triggers and Stored Procedures
• User Defined Types/Functions/Aggregates
• All SQL Server 2005 Programmability Features
• Scalability and Performance
• Supports 1 CPU, 1 GB RAM (Buffer Pool Size), 4 GB
  DB Size (not T.logs)
• No workload governor

10
Overview of SQL Server 2005 Express
Replaces MSDE

• Issues with MSDE
• Difficult deployment
• No tools for manipulating/querying the database
  once deployed
• Performance limitations the performance governor

11
Feature Comparison
12
Feature Comparison cont
13
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)

14
Other Features

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

15
Advanced Services / Toolkits Feature set
16
Licencing!!!!

• Its FREE!!!! (isnt it?)
• Yes!!!
• (Registration requirement for redistribution)

17
SQL Express Setup

• 2.0 .Net framework is a pre-requisite
• Simplified Setup UI
• Hide Advanced Configuration checkbox
• Supports silent install
• Default instance name is SQLEXPRESS
• Multiple applications or app vendors can share an
  instance
• Use specific named instance only for customized
  configuration needs
• Merge Modules not supported

18
SQL Server Express Installation.
http//msdn.microsoft.com/vstudio/express/sql/down
load/
19
SQL Server Express Installation.

• Pre-requisites
• Microsoft .NET Framework 2.0
• (22Mb)
• Microsoft SQL Server 2005 Express Edition with
  Advanced Services SP1
• (234Mb)
• Microsoft SQL Server 2005 Express Edition Toolkit
  SP1
• (213Mb)
• SQL Server 2005 Samples
• (19Mb)
• SQL Server 2005 Books Online (April 2006)
• (121Mb)
• Register SQL Server 2005 Express Edition and you
  will receive additional benefits such as
  royalty-free images from Corbis. Learn more about
  registration benefits.

20
Rich GUI Install experience - Virtual DEMO
21
SQL Express Advanced Services - Virtual DEMO
22
SQL Express Toolkit - Virtual DEMO
23
Additional Tools - Virtual DEMO
24
SQL Server 2005 Express Edition toolset

• SQL Server 2005 Surface Area Configuration
• SQL Server Configuration Tool
• Reporting Services Configuration
• SQLCMD
• SQL Server Management Studio Express
• SQL Server Business Intelligence Studio

25
SQL Server 2005 Surface Area Configuration
26
SQL Server Configuration Tool
No remote Access!!!
27
Reporting Services Configuration
28
SQLCMD
29
SQL Server Management Studio Express
30
SQL Server Business Intelligence Studio
31

• SSEUtil.exe
• http//www.microsoft.com/downloads/details.aspx?fa
  milyidfa87e828-173f-472e-a85c-27ed01cf6b02displa
  ylangen

32

• DEMO
• SQL Express Toolset
• Full Text Indexing

33

• Cool SQL Express Features Time.
• Xcopy Deployment
• User Instance Model
• (RANU Run As Normal User)

34

• XCOPY Deployment
• Ability to copy app Dbname.mdf
• No further Configuration required
• SQL Express must be present and running (Doh!)

Application Connection String Server.\SQLExpress
AttachDbFilenamec\mydb.mdf DatabasemyDBTrust
ed_ConnectionYes Or (MyDB.mdf in same folder as
app.exe) Server.\SQLExpress AttachDbFilenameDa
taDirectorymydbfile.mdf DatabasedbnameTrusted_
ConnectionYes
DEMO.
35

• User Instances (aka child or client instances)
• Requires primary instance to be running
• Isolation between non-admin users
• user instance true Keyword in connection
  string
• User instance activated by connection.open()
• User connects as SYSADMIN to instance

conn string "Data Source.\\SQLExpress"
"integrated securitytrue" "attachdbfilenameD
ataDirectory\mydb.mdf" "user
instancetrue" string connstr
GetConnectionString() // get from
config using(SqlConnection conn new
SqlConnection(connstr)) // this will connect
to the user instance, not to the // default
SSE instance conn.Open() // use the
connection to the user instance
36

• Full Text Indexing Demo
• Available SP1

37

• SQL Server Express Edition Reporting Services
• Report Server Architecture
• Enterprise features NOT supported
• Subscriptions
• Email
• Report Builder
• Demo

38
Report Server Architecture
URL / XML Web Service Interface
Report Server
Report Processing
SQL Server Catalog
39
SQL Server Express Reporting Services

• Report server e-mail and scale-out deployment are
  not supported in SQL Server Express.
• Subscriptions
• Report Builder -(
• Web Services ?
• The report server database must be hosted on a
  local SQL Server Express database engine
  instance.
• Data sources used in a report must also be local.
• Also missing
• No Schedules, Caching, Snapshots, Subscriptions
• SSAS, Oracle, XML, SAP, SSIS, OLEDB, ODBC data
  sources not supported(?)
• Local Data Sources only
• No Ad Hoc reporting
• TIFF, XML, CSV outputs not supported
• SSRS API not supported
• Customer Security (Roles..) and Authentication
  not supported

40

• DEMO
• Access Reports Report Manager
• Design Reports Business Intelligence Studio
• Deploy Reports

41
Useful Links SQL Express 2005 Home
Page http//www.microsoft.com/sql/editions/expr
ess/default.mspx SQL Express 2005 _at_
MSDN http//msdn.microsoft.com/sql/express/ SQL
Express blog http//blogs.msdn.com/sqlexpress/
SQL Server 2005 Express UPDATE
eNewsletter http//www.windowsitpro.com/email/SQ
L2005express/ Become a HERO and Win 100 US
!! http//msdn.microsoft.com/vstudio/express/sup
port/hero_promotion/default.aspx http//www.made
inexpresscontest.com/
42
QA Cheers Mark Morton mark.morton_at_didata.co
m.au