Migrating To Sql Server 2008

Mark Ginnebaugh, DesignMind John R. Hanson, DesignMind

Agenda • Introductions / Logistics – Mark Ginnebaugh • Migration Tools, Planning & Execution – John R. Hanson – Brief Overview of SQL Server 2008 – SQL Server Feature Pack

– SQL Server Migration Assistant – Case Study

• Intermission • Performance Benchmarks – SQL Server 2005 vs. 2008

• Q&A

About the Presenters DesignMind – www.designmind.com – Microsoft Gold Certified Partner – Based in Emeryville, CA – Custom Software, Databases, and BI Solutions on the Microsoft Platform – .NET, SQL Server, SharePoint

John R. Hanson Chief Applications Architect

DesignMind

Upgrade Resource/Benefit Comparison BENEFIT • • • • • • •

Spatial Support Filestream Support Hierarchy Id Support CDC* & Change Tracking LINQ Support Entity Framework Support ADO.NET Data Services Support

• • • • • • •

Policy Based Management (DMF) Performance Data Collection Enhanced date and time support Transact-SQL enhancements Sparse column support Service Broker enhancements SSIS / SSRS / SSAS enhancements*

• • • • • • •

Data/Backup Compression* Transparent Data Encryption* Resource Governor* Filtered Indexes/Statistics Query Optimizer / Storage Engine enhancements* Enhanced SQL Server Audit* SSRS/SSAS scalability improvements*

Significant Application, Operational or Deployment Changes

Moderate Application, Operational, or Deployment Changes

Minor Changes

* Requires Enterprise Edition

Delivering on the Vision Enterprise Data Platform • Secure, trusted platform for your data • Optimized and predictable system performance • Productive policy-based management of your infrastructure

Beyond Relational • Store and consume any type of data

• Deliver Location Intelligence within your applications

Dynamic Development • Accelerate your development with entities

• Synchronize your data from anywhere

Pervasive Insight • Integrate all your data in the Enterprise Data Warehouse • Reach all your users with scalable BI platform

• Empower every user with actionable insights

Upgrade Technical Value Proposition

Reduce Support Costs Policy-Based Management – Pro-active, flexible administration – Configuration Servers

Data Collection – Used beyond performance monitoring/tuning; e.g. workload profiling, capacity planning

Plan Guides and validation – Predictable query behavior

Important Trends Affecting SQL Server 2008 Usage

Consolidation – Greater opportunities with tools like Resource Governor and Extended Key Management – Providing flexible security in shared instances and shared servers

Virtualization – Leverages Windows Server 2008 feature – Easier to support since technologies are from single vendor – Not just about dev/test and training

Data in the Cloud – SQL Server Data Services

SQL Server 2008 Feature Pack • Most Recent Release: April 2009 • Collection of standalone packages that provide additional value for SQL Server 2008 • Includes – Data Mining for Excel – Native PHP Scripts

– BLOB Store – System CLR Types for External Applications

– Sync Framework – Upgrade Advisor! – And more…

SQL Server Migration Assistant • How to make an application originally written to run on Oracle, Access or Sybase to run on Microsoft SQL Server? • Main challenge – different SQL dialects implemented by DBMS vendors

SSMA for Oracle • Automates migration of Oracle databases to Microsoft SQL Server → Typically 80-90% of PL/SQL code gets converted immediately to Transact-SQL → Supports Oracle 8i, 9i,10g,11g → Target DB could be SQL Server 2005 or SQL Server 2008 → Includes: • Migration Analyzer • Schema and Data Migrator • Migration Tester

Solution Architecture Database Migration with SSMA Migration Analyzer Assess the Migration Project

Schema Migrator

Migrate Schema and Business Logic

Data Migrator

Migrate Data

Migration Tester

Test the Converted Database

Runtime Converter (RTC) (to be developed)

Convert the Application Test, Integrate, and Deploy

• Help migrate 2 clustered 32-bit SQL 2000 to clustered 64-bit SQL 2005 on new hardware with 26 user databases. • Identify all code dependencies in over 58000 code files in over 20 applications. • Identify all native SQL objects where deprecated terms and syntax occur, and provide code changes both as concepts and actual recoding. • Develop and implement a test plan.

• • • •

SQL Profiler SQL Upgrade Advisor SQLCMD Quest Discovery Wizard for SQL Server – Password Vulnerability Tool

• • • • • •

Red Gate SQL Compare Red Gate Data Compare SQL DB Search TextPad / regex Visual SourceSafe RML Utilities for SQL Server (Replay for Integration Testing) – ReadTrace – OStress

• Discover all SQL servers with dependencies • Identify each application and their Database dependencies

• Trace activity of production workflows for as long as time and storage permit. • Trace activity of all tests

• Use Upgrade Advisor to report on compatibility findings – must use both the servers and the trace files as inputs. • The report generated identifies those constructs that were exercised only!

• Find keywords – In the Server Objects – In the Code New keywords may need to be delimited (Bracketed) if used as object names depending on context.

– Develop “Code Chunks” • Search for each of the deprecated terms / syntax in the source code and create a report for each item or class of item. • Use regex to help limit false positives – E.G Outer Join Syntax *= or =* versus /*=a note*/

• Map chunks to App owners

• Use a tool that can decrypt all “encrypted” SQL Objects if source is not available / legacy apps • Trust the manufacturer of applications claiming SQL 2008 compliance, but test anyway… • Create Unit tests of SQL Code when possible to make sure at least one case of new syntax works. • Compare baseline performance to post conversion to watch for issues known in 2008.

Other Resources Application and Compatibility Content & Tools – Application Compatibility Blog http://blog.scalabilityexperts.com – SQL Server 2008 Upgrade Advisor http://www.microsoft.com/sql

– Upgrade Assistant • http://www.scalabilityexperts.com/default.asp?action=article&ID=43

– Reporting Services Scripter • www.sqldbatips.com/showarticle.asp?id=62SQL Server 2008 http://www.microsoft.com/sql/2008/default.mspx

– SQL Server 2008 Feature Pack – April 2009 • http://www.microsoft.com/downloads/details.aspx?FamilyID=b33d2c78-1059-4ce2-b80d2343c099bcb4&displaylang=en

Microsoft SQL Server Community & Samples http://www.codeplex.com/SqlServerSamples