SQL Server 2005 Express Jeremy Kadlec Edgewood Solutions

SQL Server 2005 Express Jeremy Kadlec Edgewood Solutions www. edgewoodsolutions. com jeremyk@edgewoodsolutions. com 410. 591. 4683

Agenda • • Introductions Session Goals Installation Path and Licensing Management Studio Primer Development Administration Additional Resources Q&A 2

Jeremy Kadlec • Edgewood Solutions (www. edgewoodsolutions. com) – Customer focused SQL Server solutions – Planning, Audits, Integration, Training, Products • Performance Tuning, Administration, Development, Upgrades, High Availability, Disaster Recovery, Database Auditing • Principal Database Engineer – jeremyk@edgewoodsolutions. com – 410. 591. 4683 • Author of numerous SQL Server resources – www. edgewoodsolutions. com/resources/articles. asp – Search. SQLServer. com – Ask the Experts – The Rational Guide to IT Project Management • NOVA SQL Co-Leader – www. novasql. com • SQL Server 2005 Adoption Rate Report – www. edgewoodsolutions. com/Edgewood. Labs/ 3

Session Goals • Answer the following questions: – What is SQL Server 2005 Express and how is it any different than the other versions of SQL 2005? – Where do I get my copy? – What is the general installation process? – How do I create a database, then the tables and code to support my application? – What are some of the basic administration tasks that I should be aware of? – Where can I find more information on SQL Server 2005 Express edition? 4

SS 2 K 5 Express Introduction • Scaled down and easy to use version of SQL 2005 – – – CPU’s = 1 Memory = 1 GB Database size = 4 GB Users = unlimited Cost = FREE • Replacement to SQL Server 2000 MSDE • Redistributed version of SQL Server for client applications • Intended for ISVs, ISPs, ASPs, web developers and hobbyists • Environments = Production, test and development 5

Express Edition Licensing • Register for SQL Server Express Edition Redistribution Rights – http: //www. microsoft. com/sql/editions/ express/redistregister. mspx 6

SS 2 K 5 Express Installation • • Prerequisites and installation order Download locations Verifying installation Post installation tasks 7

Prerequisites • Windows Installer 3. 1 (~2. 5 MB) – http: //www. microsoft. com/downloads/details. aspx? Family. ID=8 89482 fc-5 f 56 -4 a 38 -b 838 -de 776 fd 4138 c&displaylang=en • . NET Framework 2. 0 (~ 22 MB) – http: //www. microsoft. com/downloads/details. aspx? familyid=08 56 eacb-4362 -4 b 0 d-8 edd-aab 15 c 5 e 04 f 5&displaylang=en • Microsoft Core XML Services (MSXML) 6. 0 (~3. 5 MB) – http: //www. microsoft. com/downloads/details. aspx? familyid=99 3 c 0 bcf-3 bcf-4009 -be 21 -27 e 85 e 1857 b 1&displaylang=en 8

Express Edition Downloads • Microsoft SQL Server 2005 Express Edition (~55 MB) – http: //www. microsoft. com/downloads/details. aspx? fa milyid=220549 b 5 -0 b 07 -4448 -8848 dcc 397514 b 41&displaylang=en • Microsoft SQL Server Management Studio Express - Community Technology Preview (CTP) November 2005 (~30 MB) – http: //www. microsoft. com/downloads/details. aspx? fa milyid=82 afbd 59 -57 a 4 -455 e-a 2 d 61 d 4 c 98 d 40 f 6 e&displaylang=en 9

Verify Installation • Default Installation Directory – C: Program FilesMicrosoft SQL ServerSQLExpress – ~140 MB • Windows Service – SQL Server (SQLExpress) – SQL Server Browser • Windows Event Log 10

Post Installation Task 1 • Configuration Manager – – Services Network Protocols Client Protocols Aliases • Great for server consolidation projects without changing front end application connection strings 11

Post Installation Task 2 • Surface Area Configuration – Services and Connections • Service management • Remote Connections – Features • CLR Integration • xp_cmdshell 12

SQL Server 2005 Express Tour • Primary interface to SS 2 K 5 Express – – Object Explorer Template Explorer Summary Window View Toolbars • Combination of Enterprise Manager and QA in SQL 2000 • Similar functionality as the Management Studio for other SQL 2005 versions 13

Database Creation • Right click on the ‘Database’ folder, select ‘New Database’ and complete ‘New Database’ Wizard • CREATE DATABASE T-SQL statement 14

Table Creation • Table creation interface with Column and Table Properties – Table Designer toolbar – Save Change Script • Template Explorer – CREATE TABLE T -SQL template 15

Database Design • Create and drop tables, indexes, primary keys, etc. in the diagram or database – NOTE – Making actual coding changes, not mock up • Database Design Toolbar 16

Views • View = virtual table to query based on an underlying SELECT statement • View Designer Toolbar • View Template Explorer 17

Synonyms • Synonym = reference to a virtual object that can be on another server or schema • SQL Server imposes late binding so test based on name appropriately • CREATE SYNONYM T-SQL statement 18

Programming • T-SQL and CLR support – CLR off by default = Enable CLR via Surface Area Configuration • Objects – Stored Procedures, Functions, Triggers (DML and DDL) – Foundation for SQL Server development 19

T-SQL Enhancements • Error Handling – TRY and CATCH paradigm from procedural languages such as VB BEGIN TRY T-SQL Code… END TRY BEGIN CATCH T-SQL Code… ERROR_NUMBER() ERROR_SEVERITY() ERROR_STATE() ERROR_PROCEDURE() ERROR_LINE() ERROR_MESSAGE() END CATCH 20

T-SQL or CLR • T-SQL – Data driven logic • CLR – Extend the capabilities of the native DBMS – VB. NET, ASP. NET, C#, etc. • Word to the wise… – Keep it simple – Standardize development practices at organization – Always consider performance implications 21

XML • XML Usage – Data exchanges - B 2 B – Non traditional data - Visio diagrams • XML not replace traditional database design – XML = DDL – XQuery = T-SQL • SELECT’s FOR XML option – Auto, Raw, Explicit • Native XML data type – Columns (2 GB), variables, parameters • XML Schema – Schema Collections • XML Indexes – Primary – 1 row per node (element, attribute, text) to improve speed to the node – Secondary – Path, Value, Property • Compliments SQLXML – Update. Grams - Insert, update, or delete relational data – Diff. Grams - Modify relational data 22

Security • Login and user paradigm • Server, database and application roles – Fixed and user defined • • Schema – Container for object ownership Asymmetric Keys Symmetric Keys Certificates 23

Administration 101 • Database Backups and Restores • SQL Server Error Logs – Issue reviewing logs - C: Program Files Microsoft SQL ServerSQLExpress MSSQL. 1 MSSQLLOG • Activity Monitor – Snapshot of SQL Server transactions 24

Performance Tuning • Dynamic Management Views (DMV) operate in near real time from internal structures at a Server and Component level – – – – – dm_exec_* = Execution of user code and associated connections dm_os_* = Memory, locking and scheduling dm_tran_* = Transactions and isolation dm_io_* = I/O on network and disks dm_db_* = Databases and database objects dm_repl_* = Replication dm_broker_* = SQL Service Broker dm_fts_* = Full Text Search dm_qn_* = Query Notifications dm_clr_* = Common Language Runtime 25

How DMVs Improve Management • Index-related DMVs – sys. dm_db_index_physical_stats • Size and fragmentation information for tables and indexes – sys. dm_db_index_operational_stats • Internals information for table and index activities – sys. dm_db_index_usage_stats • Index statistics and usage counts information for individual indexes – sys. dm_db_index_partition_stats • Page and row-count information for every partition 26

Alternative Dev Environment • SQLCMD – Command line interface for any version of SQL Server 2005 – Ability to perform any development or administrative function – Dedicated Administrator Connection (DAC) – Default location = C: Program FilesMicrosoft SQL Server90Toolsbinn – More information - SQLCMD /? 27

Patching Express • SQL Slammer was able to cause havoc, propagating a DOS and needed patching – Patch SQL Servers – Use a non-default port and other settings • Expectation is patching will be equal to SQL Server Service Packs or Windows Updates • Stay tuned… 28

SS 2 K 5 Express Web Resources • Microsoft SQL Server 2005 Express Web Site – http: //www. microsoft. com/sql/editions/express/ default. mspx • MSDN Web Site – SQL Server 2005 Express – http: //msdn. microsoft. com/sql/express/ • SQL Server 2005 Express BLOG – http: //blogs. msdn. com/sqlexpress/ • SQL Server 2005 Books Online – http: //www. microsoft. com/technet/prodtechnol/sql/ 2005/downloads/books. mspx 29

SS 2 K 5 Express Books • SQL Server 2005 Express Beta Preview – http: //www. Mann. Publishing. com/ • Wrox's SQL Server 2005 Express Edition Starter Kit • Microsoft (r) SQL Server (tm) 2005 Express Edition: Step by Step • Sams Teach Yourself SQL Server 2005 Express in 24 Hours • Microsoft SQL Server 2005 Express For Dummies® 30

Questions and Thank You Jeremy Kadlec Edgewood Solutions www. edgewoodsolutions. com jeremyk@edgewoodsolutions. com 410. 591. 4683 31