1 / 55

Upgrading To SQL Server ™ 2000 Name Title Department Microsoft Corporation

Upgrading To SQL Server ™ 2000 Name Title Department Microsoft Corporation. Session Prerequisites . This session assumes that you are familiar with Windows NT ® 4.0 and/or Windows ® 2000 SQL Server 7.0/2000 to some degree General database administrative tasks and procedures

nero
Download Presentation

Upgrading To SQL Server ™ 2000 Name Title Department Microsoft Corporation

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. Upgrading To SQL Server™ 2000NameTitleDepartmentMicrosoft Corporation

  2. Session Prerequisites • This session assumes that you are familiar with • Windows NT® 4.0 and/or Windows® 2000 • SQL Server 7.0/2000 to some degree • General database administrative tasks and procedures • This is a level 200 session

  3. Session Topics • Preparing for SQL Server 2000 • Upgrading from 6.5 to 2000 • Upgrading from 7.0 to 2000 • Post-installation considerations

  4. Preparing For SQL Server 2000 • Hardware and software • Editions and functionality • Security • Changes to install • Design considerations • Backward compatibility

  5. Hardware/Software Requirements • Windows NT 4.0 with SP5 or Later • Internet Explorer 5.0 or Later • SQL Server 6.5 (SP5 or SP3) • SQL Server 7.0 • Connectivity only • With no encryption only need Internet Explorer 4.01 SP2

  6. Editions And Functionality • Enterprise/Developer Edition • Multiple Instance • Failover Clustering • Log Shipping • Parallel DBCC • Parallel Create Index • Index Views • Federated DB Servers • Full-Text Search • Full Replication Support • Standard Edition • Multiple Instance • Graphical Utils • Full-Text Search • Full Replication Support • Personal Edition • Multiple Instance • Graphical Utils • Full-Text (not on 98) • No Subscriber Transactional • Desktop Edition • Multiple Instance • No Subscriber Transactional

  7. SQL Server 2000 Testing • 478 stress tests as compared to 158 for SQL Server 7.0 • Long haul stress • 40 Microsoft® Internal Business Systems versus 24 for SQL Server 7.0 • 201,000 Beta Members • 100+ Rapid Deployment Customers

  8. Some Of The Customers • Buy.com • http://www.microsoft.com/servers/net/buy.htm • Chevron Canada Limited • http://www.microsoft.com/servers/net/chevronca.htm • Lexis-Nexis • http://www.microsoft.com/servers/net/lexnex.htm • Turner Broadcasting • http://www.microsoft.com/servers/net/turner.htm

  9. Security • Registered when… • During server startup • Register/update service connection points • Registers missing databases • Updates database properties • After backup • Customized

  10. Design Considerations • Collations at the DB, column, server level • Multi-instance support • Log shipping • Clustering

  11. Instances { SQL Server 6.5 • Default • Only one active • Version switch • Named • All instances can be active { SQL Server 7.0 or 2000 SQL Server 2000 SQL Server 2000 SQL Server 2000 Up to 15 SQL Server 2000

  12. LogBackups Log Shipping • Database restore not required BACKUP LOG … TO … WITH { NORECOVERY | STANDBY } • Log backups taken on secondary can be applied back on primary • Very useful for planned failover Full Backup

  13. Failover Clustering Installation • No external wizard • Cluster management • Add and remove nodes • Multiple IP addresses • Multiple virtual servers – each with a single instance • No unclustering in SQL Server 2000 when applying service packs • Upgrade to a cluster

  14. Unattended Installation • Generates an (*.iss) File • Integrated as part of the setup • No more k=rc • Now supports upgrade

  15. Upgrading And Working With Access 2000 And SQL Server 2000 • Need SR-1 • Diagrams • Alter but no Save • Access Upsizing Wizard • Warning dialog…

  16. Backward Compatibility With SQL Server 7.0 • Master – target servers • Changes to Backup/Restore • New reserved keywords • COLLATE, FUNCTION, OPENXML • Profiler extended stored procedures have been changed significantly

  17. Backward Compatibility With SQL Server 6.5 • Major changes to backup • Many configuration options changed or set to auto-tune • New keywords • Certain system tables removed • sysprocedures, systasks, sysusages, syslocks, and backup and restore tables

  18. Scalability Enhancements • Federated Database Servers • Allows updateable views of the data • Spread across multiple local or remote instances • Partitioning handled via check constraints • Only one constraint on the column • Must be part of the primary key • Among other considerations….

  19. SRV 3 Orders DB Check CustID > 2,000,000and <=3,000,000 SRV 2 Orders DB Check CustID > 1,000,000and <= 2,000,000 SRV1Orders DB Check CustID <= 1,000,000 Federated Database Servers Total Customer View 1 ~ ~ ~ ~ Insert 2 ~ ~ ~ ~ 3 ~ ~ ~ ~ 4 ~ ~ ~ ~ 5 ~ ~ ~ ~ 6 ~ ~ ~ ~ Update Customers Set CustID = 2,000,001 Where CustID = 1,234 Delete Customers ~ ~ ~ n ~ ~ ~ n 1 ~ ~ ~ ~ ~ ~ ~ n ~ ~ ~ y 2 ~ ~ ~ ~ ~ ~ ~ y ~ ~ ~ y 3 ~ ~ ~ ~ ~ ~ ~ y ~ ~ ~ n 4 ~ ~ ~ ~ ~ ~ ~ n ~ ~ ~ y 5 ~ ~ ~ ~ ~ ~ ~ y 6 ~ ~ ~ ~

  20. Demo 1 • Unattended installation • Now supports upgrade • Federated database servers • Multi-instance support • Distributed partitioned views

  21. Upgrading From 6.5

  22. Pre-Upgrade Checklist(6.5) • Backup all *.dat files • Run DBCC commands • Disk space issues • Need approx. 1.5 times the disk space • Tempdb > 25MB • Tempdb > 10MB free space • Master > 3MB free space

  23. Pre-Upgrade Checklist(6.5) • Ensure all database users have logon information in the master DB so objects are created • If DB1 is the upgrade target but login1 has default database of DB3, DB3 must be upgraded as well • User running upgrade wizard must be administrator on both upgrade and import servers • Two computer upgrade • MSSQLSERVER service needs a domain login account with administrator rights on the local machine

  24. Upgrade Performance (6.5) • 400 MB • < 20 minutes • 1 GB • < 1 hour • 10 GB • < 4 hours • 50 GB • < 12 hours • 100 GB • < 24 hours • Selecting data validation can increase this time two-fold

  25. Performing The Upgrade • Data transfer method • Tape or named pipes conversion • Upgrade verification options • Choosing the databases to upgrade

  26. SQL Upgrade Wizard • Options • Objects to transfer • Server configuration • Replication settings • SQL Executive settings • ANSI NULLS ON|OFF • QUOTED_IDENTIFIERS ON|OFF

  27. Database Layout • Database files • MDF in location of first data device • LDF in location of first log device • Multiple database files • 1 MDF for the bulk of the data • Minimal size MDF”s for each other data device • 1 LDF for the bulk of the log • Minimal size LDF’s for each other log device • Have the ability to customize

  28. 100MB Data 200MB Data Min Size Data 100MB Data 100MB Log 200MB Log 100MB Log Min Size Log Database Layout • Default Behavior • Can Customize in Wizard or with Script Data Logs

  29. Setup Files Troubleshooting • Placed in the Upgrade Directory • Specific directory for each DB upgraded • Naming convention Errors will appear as follows: MSSQL\Upgrade\<SERVERNAME_DATE_TIME> will contain *.ERR files Inconsistency in Database Objects MSSQL\UPGRADE\<SERVERNAME_DATE_TIME>\CHECK65-<DBID>-<DBNAME>_ERR.OUT

  30. Demo 2 • Upgrade a 6.5 Database to SQL Server 2000

  31. Pop Quiz • How do you browse for SQL Servers in the Active Directory™? • With standard Active Directory browsing tools • What SQL Server feature lets you horizontally partition databases? • Federated database servers • What three new keywords exist in SQL Server 2000 when upgrading from 7.0? • Collate, OpenXML, Function

  32. Upgrading From 7.0

  33. Upgrade Methods • Installation upgrade • Upgrades the server in place • Left with a SQL Server 2000 server alone • Backup and restore • Only restore 7.0 databases • No upgrade for: • Attach and detach • You can attach a 7.0 database onto a SQL Server 2000 server MSDB Model Distribution Master

  34. Demo 3Part A and Part B • Upgrade via Backup • Upgrade via Detach and Attach

  35. CDW Architecture Data flow Destination SQL Server 2000 Source SQL Server 7.0 or 2000 PULL 3: SQL Agent Executes DTS Pkg Connect toSrc. and Dst. Server 1: Read DB and Schema information 2: Create DTS Package on Destination Copy Database Wizard NOTE: All Processes run on Destination

  36. CDW Restrictions • Database Name Changes During the Copy/Move Are Not Allowed • Limitations • Replicated • Log Shipped • Windows® 9x sources need to create share • SysAdmin rights • SQLAgent run under domain account

  37. Demo 3Part C • Copy Database Wizard

  38. Post-Installation Issues

  39. Backward Compatibility Levels • Use as an interim migration aid • Upgrade from 7.0 compatibility is changed to 8.0 • Upgrade from 6.5 compatibility level is maintained • Sp_dbcmptlevel • Enterprise manager

  40. Full-Text Indexes And Stats • After upgrade… • Databases need to be full-text enabled • Catalogs need to be repopulated • Statistics for Tables/Indexes need to be repopulated • Sp_updatestats

  41. Replication • Order of upgrade • Distributor > Publisher > Subscriber • Immediate update concerns • Need to upgrade both Publisher and Subscriber before replicating data • Drop the publication and all subscriptions • Drop the timestamp column from pub and sub tables • Re-create the publication • Rows now use a uniqueidentifer column instead of a timestamp

  42. Replication • FTP Replication • Properties now stored at publisher so no need to change each subscriber • But… • FTP replication will be turned off after upgrade • Only affects Snapshot or Merge/Transactional publications that need a snapshot

  43. Profiler • Profiler in SQL Server 7.0 had a set of extended stored procedures • Used to implement custom trace programs • All have been changed • C2 security enhancements • Profiler no longer drops trace elements when under heavy load

  44. Imaging And Server Rename Issues • Imaging a SQL Server for distribution…. • Required dealing with name changes manually • SQL Server 2000 changes this • Server name is detected on the first boot after install • If different than that stored in SQL Server, SQL Server is updated

  45. Configuring The Server • Memory • Dynamically allocated by default • Security settings • Windows authentication • SQL Server and Windows • Connections • Upgrading from 6.5 no longer a hard cap • Ability to change default DB and file locations

  46. Backup And Restore Considerations • Many database options have changed • Select Into/BulkCopy • Truncate Log on Ckpt. • Recovery Models • Simple • Bulk-Logged • Full

  47. Using SQLDIAG.EXE • SQLDIAG.EXE • Located in the program files\microsoft sql server\Binn directory • Outputs by default to program files\microsoft\sql server\log directory • Outputs information on current processes, configuration information, databases, etc.

  48. Demo 4 • Updating full-text indexes and statistics • Setting server configuration settings • Understanding recovery models • Using SQLDIAG.EXE

  49. Pop Quiz • What feature of replication is disabled after upgrade? • FTP replication • What recovery model minimizes logging but still let’s you use log backups? • Bulk-logged • When upgrading from SQL Server 6.5, what compatibility level is maintained by default? • 6.5 • What two ways can you change compatibility level? • Sp_dbcmptlevel and the Enterprise Manager

  50. Microsoft Curriculum Courses • 2071 • Querying Microsoft SQL Server 2000 using Transact-SQL • 2072 • Administering Microsoft SQL Server 2000 • 2073 • Implementing a Database Design on Microsoft SQL Server 2000

More Related