JKadlec SQLServer2005Express Rev2

Download
Share  Add to  Flag Embed
Uploaded from authorPOINTLite
Views:
 
Category: Education
     
 
License:   All Rights Reserved

Presentation Description

No description available.

Comments

 

By: satheyaraaj (18 month(s) ago)

nice

 

By: hadi1982 (20 month(s) ago)

but we need more

 

By: hadi1982 (20 month(s) ago)

nice

 

By: moshkelh2000 (43 month(s) ago)

i found this presentation very good and useful to the new sql server 2005 developer. it cover the basics. but i need to download ppt may i ask its ppt?

Presentation Transcript

SQL Server 2005 Express: 

SQL Server 2005 Express Jeremy Kadlec Edgewood Solutions www.edgewoodsolutions.com jeremyk@edgewoodsolutions.com 410.591.4683

Agenda: 

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

Jeremy Kadlec: 

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 SearchSQLServer.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/EdgewoodLabs/

Session Goals: 

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?

SS2K5 Express Introduction: 

SS2K5 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

Express Edition Licensing: 

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

SS2K5 Express Installation: 

SS2K5 Express Installation Prerequisites and installation order Download locations Verifying installation Post installation tasks

Prerequisites : 

Prerequisites Windows Installer 3.1 (~2.5 MB) http://www.microsoft.com/downloads/details.aspx?FamilyID=889482fc-5f56-4a38-b838-de776fd4138c&displaylang=en .NET Framework 2.0 (~ 22 MB) http://www.microsoft.com/downloads/details.aspx?familyid=0856eacb-4362-4b0d-8edd-aab15c5e04f5&displaylang=en Microsoft Core XML Services (MSXML) 6.0 (~3.5 MB) http://www.microsoft.com/downloads/details.aspx?familyid=993c0bcf-3bcf-4009-be21-27e85e1857b1&displaylang=en

Express Edition Downloads : 

Express Edition Downloads Microsoft SQL Server 2005 Express Edition (~55 MB) http://www.microsoft.com/downloads/details.aspx?familyid=220549b5-0b07-4448-8848-dcc397514b41&displaylang=en Microsoft SQL Server Management Studio Express - Community Technology Preview (CTP) November 2005 (~30 MB) http://www.microsoft.com/downloads/details.aspx?familyid=82afbd59-57a4-455e-a2d6-1d4c98d40f6e&displaylang=en

Verify Installation: 

Verify Installation Default Installation Directory C:\Program Files\Microsoft SQL Server\SQLExpress\ ~140 MB Windows Service SQL Server (SQLExpress) SQL Server Browser Windows Event Log

Post Installation Task 1: 

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

Post Installation Task 2: 

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

SQL Server 2005 Express Tour: 

SQL Server 2005 Express Tour Primary interface to SS2K5 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

Database Creation: 

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

Table Creation: 

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

Database Design: 

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

Views: 

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

Synonyms : 

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

Programming: 

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

T-SQL Enhancements: 

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

T-SQL or CLR: 

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

XML: 

XML XML Usage Data exchanges - B2B 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 UpdateGrams - Insert, update, or delete relational data DiffGrams - Modify relational data

Security: 

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

Administration 101: 

Administration 101 Database Backups and Restores SQL Server Error Logs Issue reviewing logs - C:\Program Files\ Microsoft SQL Server\SQLExpress\ MSSQL.1\ MSSQL\LOG Activity Monitor Snapshot of SQL Server transactions

Performance Tuning: 

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

How DMVs Improve Management: 

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

Alternative Dev Environment: 

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 Files\Microsoft SQL Server\90\Tools\binn More information - SQLCMD /?

Patching Express: 

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…

SS2K5 Express Web Resources: 

SS2K5 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

SS2K5 Express Books: 

SS2K5 Express Books SQL Server 2005 Express Beta Preview http://www.MannPublishing.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®

Questions and Thank You: 

Questions and Thank You Jeremy Kadlec Edgewood Solutions www.edgewoodsolutions.com jeremyk@edgewoodsolutions.com 410.591.4683