1 / 53

Your Data Any Place, Any Time

Your Data Any Place, Any Time. Upgarde to Sql Server 2008. Dubi Lebel Data platform Technology Manager Solution & Technology Group Microsoft Israel dubil@Microsoft.com. WWW.SQL.CO.IL. using System; using System.Collections.Generic ; using System.ComponentModel ;

kagami
Download Presentation

Your Data Any Place, Any Time

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Your Data Any Place, Any Time Upgarde to Sql Server 2008 Dubi Lebel Data platform Technology Manager Solution & Technology Group Microsoft Israel dubil@Microsoft.com

  2. WWW.SQL.CO.IL

  3. using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; using System.Collections.ObjectModel; using System.Configuration; namespace EnterDates { public partial class Form1 : Form { BindingSource bindingSource1 = new BindingSource(); string connString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString; public Form1() { InitializeComponent(); ReadOnlyCollection<TimeZoneInfo> tzCollection1; tzCollection1 = TimeZoneInfo.GetSystemTimeZones(); this.comboBox1.DataSource = tzCollection1; } private void button1_Click(object sender, EventArgs e) { using (SqlConnectionconn = new SqlConnection(connString)) using (SqlCommandcmd = new SqlCommand("insert into datetest values(@dtoff, @entered)", conn)) { cmd.Parameters.Add("@dtoff", SqlDbType.DateTimeOffset); cmd.Parameters.Add("@entered", SqlDbType.VarChar, 100); TimeZoneInfoselectedTimeZone = (TimeZoneInfo) this.comboBox1.SelectedItem; DateTimeOffsetdt_here = DateTimeOffset.Now; DateTimeOffsetdt_there = TimeZoneInfo.ConvertTime(dt_here, selectedTimeZone); cmd.Parameters[0].Value = dt_there; if (selectedTimeZone.IsDaylightSavingTime(dt_there)) cmd.Parameters[1].Value = selectedTimeZone.DaylightName; else cmd.Parameters[1].Value = selectedTimeZone.StandardName; conn.Open(); inti = cmd.ExecuteNonQuery(); if (i == 1) MessageBox.Show("New row has been entered"); } } private void button2_Click(object sender, EventArgs e) { SqlDataAdapterda = new SqlDataAdapter(); da.SelectCommand = new SqlCommand(); da.SelectCommand.Connection = new SqlConnection(connString); TimeZoneInfoselectedTimeZone = (TimeZoneInfo)this.comboBox1.SelectedItem; DateTimeOffsetdt_here = DateTimeOffset.Now; DateTimeOffsetdt_there = TimeZoneInfo.ConvertTime(dt_here, selectedTimeZone); ////inti = ; //// major kludge... //string s1 = dt_there.ToString(); //string[] parts1 = s1.Split('-'); //string[] parts2 = s1.Split('+'); //if (parts1.Length == 2) // da.SelectCommand.CommandText = String.Format("select switchoffset(datetimecol, '-{0}') as [EnteredDateTime], * from datetest", parts1[1]); //else //string offset = selectedTimeZone.BaseUtcOffset.ToString();//.Substring (0,5+ selectedTimeZone.BaseUtcOffset.Hours<0?1:0) ; da.SelectCommand.CommandText = String.Format("select switchoffset(datetimecol, {0}) as [EnteredDateTime], * from datetest", selectedTimeZone.BaseUtcOffset.TotalMinutes ); string s = da.SelectCommand.CommandText; dataGridView1.DataSource = bindingSource1; // Populate a new data table and bind it to the BindingSource. DataTable table = new DataTable(); table.Locale = System.Globalization.CultureInfo.InvariantCulture; da.Fill(table); bindingSource1.DataSource = table; // Resize the DataGridView columns to fit the newly loaded content. dataGridView1.AutoResizeColumns( DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader); } } }

  4. Your Data Any Place, Any Time Enterprise Data Platform Services Reporting Integration Analysis Synch Beyond Relational Query Search FILE RDBMS OLAP Dynamic Development XML Mobile and Desktop Server Cloud Pervasive Insight

  5. Microsoft BI Suite DELIVERY END USER TOOLS & PERFORMANCE MANAGEMENT APPS BI PLATFORM (RDBMS, ETL, OLAP, Reporting)

  6. Our End-to-End BI Offering DELIVERY COLLABORATION CONTENT MANAGEMENT SharePoint Server SEARCH Reports Dashboards Excel Workbooks Analytic Views Scorecards Plans END USER TOOLS & PERFORMANCE MANAGEMENT APPS Excel PerformancePoint Server BI PLATFORM SQL Server Reporting Services SQL Server Analysis Services SQL Server DBMS SQL Server Integration Services

  7. SQL Server 2008 • SQL Server Change Tracking • Synchronized Programming Model • Visual Studio Support • SQL Server Conflict Detection • FILESTREAM data type • Integrated Full Text Search • Sparse Columns • Large User Defined Types • Date/Time Data Type • LOCATION data type • SPATIAL data type • Virtual Earth Integration • Partitioned Table Parallelism • Query Optimizations • Persistent Lookups • Change Data Capture • Backup Compression • MERGE SQL Statement • Data Profiling • Star Join • Enterprise Reporting Engine • Internet Report Deployment • Block Computations • Scale out Analysis • BI Platform Management • Export to Word and Excel • Author reports in Word and Excel • Report Builder Enhancements • TABLIX • Rich Formatted Data • Personalized Perspectives • … and many more • Transparent Data Encryption • External Key Management • Data Auditing • Pluggable CPU • Transparent Failover for Database Mirroring • Declarative Management Framework • Server Group Management • Streamlined Installation • Enterprise System Management • Performance Data Collection • System Analysis • Data Compression • Query Optimization Modes • Resource Governor • Entity Data Model • LINQ • Visual Entity Designer • Entity Aware Adapters

  8. Anatomy of an Upgrade

  9. Before We Begin Goals Upgrade considerations Pre / post upgrade tasks Tools & methodology Upgrade options and paths Understand changes/backward compatibility issues Non-goals Exhaustive list of technical Issues Client Application No substitute for knowing your application! In-depth drill down on SQL Server 2008 features But feel free to ask questions End-to-end coverage of SQL Server 2008 It is a very big product; there are separate tracks/content dedicated to each area Pre-requisites / Assumptions Intermediate level technical knowledge of SQL Server 2000/2005 Basic knowledge of SQL Server 2008

  10. Agenda • Before you run setup.exe • Upgrade methodology overview • Upgrade options and considerations • Upgrade tools

  11. Before you run setup.exeMicrosoft resources to review before upgrading • Upgrade Advisor • Look in \Servers\Redist\Upgrade Advisor , but.... • Download the last version from the web • SQL Server 2008 Books Online • Review de-supported (removed) features • These have been deprecated for at least 2 versions • Transparent Benefits of Upgrading to SQL Server 2005 • Applicable for upgrades from SQL Server 2000 • http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032285808&CountryCode=US

  12. Agenda • Before you run setup.exe • Upgrade methodology overview • Upgrade options and considerations • Upgrade tools

  13. Upgrade Methodology Overview • Identify Upgrade Requirements • SQL Server edition • Hardware considerations • Run Upgrade Advisor • Resolve identified issues • Perform Application Compatibility Testing • Ensure thorough testing of all RDBMS queries • Pre-Upgrade Considerations • Document existing system • Develop validation tests / performance benchmarks and capture baseline data • Formulate and test rollback plan • May required 3rd party applications • Determine Appropriate Upgrade Strategy • In-place or side-by-side (migrate) • Upgrade Process • Pre-upgrade tasks • Post Upgrade Considerations • Immediate / short-term / medium-term / long-term

  14. Upgrade Advisor Overview • Analyzes • Configuration and objects within installed components • SQL Server • Analysis Services • Notification Services • Reporting Services • Data Transformation Services • Integration Services • Trace files (captured using Profiler/SQL Trace) • T-SQL scripts (any script file) • Generates report of issues that must be addressed • Before – potential showstoppers, must resolve before upgrading • After – should be resolved after completing upgrade • Anytime • Advisory – notices and/or warnings

  15. Requirements • Windows XP SP2, Windows Server 2003 SP1, Windows Vista, Windows Server 2008 Beta 3 • Windows Installer 3.1 • .NET Framework 2.0 • Supports • SQL Server 2000 • Requires decision support objects (DSO) if scanning Analysis Services 2000 • Requires client components if scanning SQL Server 2000 packages • SQL Server 2005 • Requires backward compatibility components if scanning SQL Server 2005 DTS packages that were upgraded from SQL Server 2000

  16. Getting Started • Run Upgrade Advisor from programs menu • Start with documentation • Access from Welcome screen or navigate to installation folder • Review rules included with current version of UA • Check for updates • Ensure new rules are not missed • Launch • Analysis Wizard • Report Viewer

  17. Analysis Wizard: Server selection and components • Server name • Local or Remote • Supports named instance (see next section) • Detect components • Select one or more to analyze

  18. Analysis Wizard: Connection parameters • Instance name • Select from drop-down for local instances • Enter instance name manually if not found or for remote instances • MSSQLServer is the default instance name • Select authentication and user name • Make sure you have necessary permissions to read objects

  19. Analysis Wizard: SQL Server parameters • Select databases to be analyzed • Pick only the ones you will upgrade • Recommend analyzing databases with large number of objects separately • Trace files • Use a workload trace that presents a good representation of your application’s functionality (broad feature coverage) • Can extend analysis time notably • Batch files • Useful for maintenance and batch scripts

  20. Analysis Wizard: Confirm settings • Final review before running analysis • Note report file path • If running more than once, backup files/folder – new report will overwrite existing report

  21. Analysis Wizard: Analysis in progress • Scans components, trace files and script files • Applies rules against scanned objects to detect compliance/violation • Each object is compared with all relevant rules • Currently 87 (CTP5), can be added/upgraded independent of SQL Server • Analysis period can range from minutes to days, depending on • Number of objects in component (SQL Server, Analysis Services, DTS, etc…) • Size of trace file and/or script file • Disk performance • Click on “Launch Report”when completed

  22. Analysis Wizard: Analysis completed

  23. Agenda • Before you run setup.exe • Upgrade methodology overview • Upgrade options and considerations • Upgrade tools

  24. Upgrade Path Options • Versions • SQL Server 2000 SP4 or later • SQL Server 2005 SP2 or later • Components • Database Engine • Includes sub-components like SQL Agent, Full-text, Tools, etc. • Reporting Services • IIS to http.sys? • Notification Services 2005 only (deprecated in 2008) • Analysis Services (it is recommended that cubes are migrated) • Data Transformation Services • Integration Services • Editions • Desktop, Workgroup, Personal, Standard, Developer, Enterprise • Platforms • 32-bit & 64-bit (IA64 and x64) • Languages • All SQL Server 2000 and SQL Server 2005 released languages

  25. Upgrade Options and Considerations: In-Place • Similar to 20002005 upgrade • Updates an existing installation while preserving user data • Instance name remains the same after upgrade • Existing instance overwritten post-upgrade • Automated process

  26. Upgrade Options and Considerations: In-Place SQL Server 2000/2005 Instance SQL Server 2008 Instance Upgrade

  27. Upgrade Options and Considerations: In-Place Point of no return Instance is still available Attach resource database Redirect services to new binaries Install SQL Server 2008 binaries Restart service Execute various upgrade scripts Install prerequisites Upgrade Complete Upgrade Starts Start service in single-user mode Stop service Stop service Start updating all databases Check for upgrade blockers Remove old binaries Instance is no longer available Partial availability starts here Instance becomes available

  28. Upgrade Options and Considerations: In-Place • Pros • Easier, mostly automated • Generally fast overall process • Requires no additional hardware • Applications remain pointing to same server/database name • Cons • Less granular control over upgrade process • Instance remains offline during part of upgrade • Not best practice for all components • Complex rollback strategy

  29. Upgrade Options and Considerations: Side-by-side • Similar to 20002005 migrate (side-by-side) • Install new instance of SQL Server • Database objects are copied between instances • Copy Database Wizard • Does not copy extended procs, alerts, DTS packages, linked servers. • Detach -> Attach • Backup -> Restore • T-SQL Scripts, BCP New and Old instance reside side-by-side • Can be same server (named instance) or different server • Mostly Manual process

  30. Compare and Verify Upgrade Options and Considerations: Side-by-side SQL Server 2000/2005 Instance SQL Server 2008 Instance Verified!

  31. Upgrade Options and Considerations: Side-by-side • Pros • More granular control over upgrade process • Database level • Can be used to perform test migration • Document process & gather metrics • Ability to run systems side-by-side for testing and verification • May require 3rd party application to keep both in-sync • Relatively straightforward rollback Strategy • Can leverage failover/switchover to reduce downtime • Cons • Usually require additional hardware • Additional resources required if on same server • Server/database name changes • Not practical for VLDB unless utilizing SAN • Beware of “loss of quick roll-back”

  32. Agenda • Before you run setup.exe • Upgrade methodology overview • Upgrade options and considerations • Upgrade tools

  33. Upgrade Tools • Upgrade Advisor • Analyzes SQL Server 2000/2005 database, trace files and script files • Read-only operation, can be CPU intensive • Supports remote execution • Supports default and named instance • Generates a report of • Detected issues • Blocking Issues • Pre-Upgrade Issues • Post-Upgrade / Migration Issues • Suggestions on how to fix / work around • Requires .NET framework 2.0 sp1 or later • Download latest version from web!

  34. Upgrade Tools • Upgrade Assistant / Database Upgrade Testing Toolkit (DUTT) • Used in addition to Upgrade Advisor • Allows testing of actual TSQL execution against SQL Server • Can detect changes in execution method, path and results • Upgrade Advisor does not • Allows testing of dynamic/embedded SQL • Leverages Profiler/Trace capability • Requires SQL Server 2008 Client Tools • Collaborative development between SQL Server Development Team and Scalability Experts • Free download from www.scalabilityexperts.com

  35. Some Known Compatibility Issues • Applications work fine on 8.0/9.0 compatibility mode but fails in 10.0 mode • Additional ANSI SQL standards enforcements in 2005 (e.g. left and right outer joins using *= and =* no longer supported) • Modify SQL statements to be standards compliant (will still work if in 8.0 compatibility mode) • References to system and/or undocumented objects • Accessing system tables/stored procedures or undocumented features may/will fail or behave differently • Modify application to utilize Dynamic Management Views and Functions (e.g. syslocks sys.dm_tran_locks) • Poorer performance after upgrade • Queries take longer to run after upgrading to SQL Server 2008 • Several possible reasons and solutions • Review queries to ensure hints are still valid (or just remove them) • Do full updated statistics (use different sampling size for large tables) • Review TempDB utilization and optimize via storage isolation • Run DB Tuning Advisor • Contact PSS if performance difference is significant • Cannot Access SQL Server after upgrade • Clients can no longer connect to the database server or related components • Check settings in Surface Area Configuration – “off-by-default” is in place for features and access methods • Cannot Upgrade Log Shipping 2000 • Log Shipping in 2008 does not use DB Maintenance Plan Wizard • Migrating with failover – switchover to standby, upgrade primary, switchback, upgrade standby, re-establish Log Shipping or other HA technology • Migrating without failover – upgrade primary, upgrade secondary, re-establish Log Shipping or other HA technology • Check http://support.microsoft.com/, msdn, technet and Books Online for latest updates.

  36. Planning An Upgrade

  37. Upgrade Methodology Overview Identify Upgrade Requirements SQL Server edition Hardware considerations Run Upgrade Advisor Resolve identified issues Perform Application Compatibility Testing Ensure thorough testing of all RDBMS queries Pre-Upgrade Considerations Document existing system Develop validation tests / performance benchmarks and capture baseline data Formulate and test rollback plan May required 3rd party applications Determine Appropriate Upgrade Strategy In-place or side-by-side (migrate) Upgrade Process Pre-upgrade tasks Post Upgrade Considerations Immediate / short-term / medium-term / long-term

  38. Pre Upgrade Tasks Document existing SQL Server solution sp_configure SQLDIAG.EXE sp_dboption SAN configurations Network configurations Security configurations Capture performance baseline data System level (e.g. perfmon, waitstats, etc…) Application level (e.g. query response, concurrent users, etc…) Develop criteria and unit / verification tests Can leverage existing user acceptance test infrastructure but be sure to update relevant components Optional: capture query plans for complex queries

  39. Pre Upgrade Tasks Ensure Clean Environment Check Database Consistency DBCCCHECKDB Consider need for shrinking databases DBCCSHRINKDATABASE DBCCSHRINKFILE Consider rebuilding indexes For faster manual copy if taking side-by-side approach Backup old instance / database Verify Backup! Loop in Windows Administrators, SAN Administrators and Network Operations Center Ensure no collisions with other activities Access to SME Emergency support contacts

  40. Post Upgrade Tasks Immediate Tasks Decision whether to rollback Ensure performance Resource Planning Short-Term Medium Term Long-Term

  41. Review all logs Revisit Upgrade Advisor recommendations Update statistics to ensure performance Full if possible Sample for very large tables. Reconfigure Log Shipping Re-populate Full-Text catalogs Verify Agent jobs and maintenance tasks Verify security settings, especially cross server and/or cross-domain access privileges Immediate Post Upgrade Tasks

  42. Check database consistency DBCC CHECKDB DBCC VERIFY Configuration Manager (not more Surface Area Configuration) Critical for Side-By-Side to reset environment, protocols, etc… Upgrade process “tries” to preserve functionality for In-Place Size and configure tempdb correctly DBCC CHECKDB, CTEs, Cursors, MARS, Row Versioning, Service Broker, Triggers Verify MODEL database (if customized) Perform unit / verification tests Decide whether to go live or rollback! Immediate Post Upgrade Tasks

  43. Short-Term Post Upgrade Tasks Data Types Take advantage of new data types From 2000: NVARCHAR(MAX), VARCHAR(MAX), VARBINARY(MAX), XML From 2000/2005: Filestream, spatial, date, time Persisted columns Index persisted columns Database options DB_CHAINING, EXECUTE AS PAGE_VERIFY CHECKSUM Evaluate AUTO_UPDATE_STATISTICS_ASYNC Evaluate PARAMETERIZATION

  44. Partitioned Tables Enterprise Edition Partitioned Views -> Partitioned Tables Database Files / Filegroups Performance Benefits sp_configure ‘affinity mask’ sp_configure ‘affinity I/O mask’ Management Benefits Database Tuning Advisor! Short-Term Post Upgrade Tasks

  45. Re-evaluate Indexed Views Definition of “determinism” has changed Custom aggregations Re-evaluate indexes Optimizer has changed Database Engine Tuning Advisor Re-evaluate statistics Sampling Automatic Manual Medium-Term Post Upgrade Tasks

  46. Medium-Term Post Upgrade Tasks Re-evaluate Optimizer Hints Remove or document reasons to retain Online operations Enterprise Edition Row-Versioning Evaluate Read-Committed Snapshot Isolation (RCSI) ALLOW_SNAPSHOT_ISOLATION READ_COMMITTED_SNAPSHOT

  47. Re-write “COM components” as CLR Revisit use of custom CLR types (e.g. date, time, spatial) Deprecated Features xp_sendmail / SQL Mail Data types There a heaps, heaps, heaps more (especially for 2000 upgrades) DTS Packages Especially if “upgrade” from 2000 to 2005 then 2008 without re-write Security Schemas Different encryption options There a heaps, heaps, heaps more especially for 2000 upgrades (much simpler for 2005) Long-Term Post Upgrade Tasks

More Related