1 / 25

SQL Migration

SQL Migration. The Migration Project. When did you begin the migration? How long did the project take? When was it completed? How did you prepare for it (data scanners, custom programming scanner, etc.?). Contra Costa Community College District.

shea
Download Presentation

SQL Migration

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. SQL Migration

  2. The Migration Project • When did you begin the migration? • How long did the project take? • When was it completed? • How did you prepare for it (data scanners, custom programming scanner, etc.?)

  3. Contra Costa Community College District • Purchased and installed new hardware in summer 2011 • Configuration and installation of the SQL Clean environment was completed October 2011 • Migration was completed April 2012 (during DUG) • Started running data scanners in summer 2011 • Began converting custom paragraphs into Envison reports fall 2011 • Rewrote Query Builder reports into SQL Reporting Services throughout the migration

  4. Marygrove College • Began project October 2010. • Did not buy servers, ended up renting since there was no budget • New servers installed March 2011 • Plan was to run 3 test migrations with final migration July 4th 2011 break. • Did not need to run DMIG. • Completed July 4th break – 4 days. • Several challenges – printing, third-party products, custom integrations

  5. DMIG • What challenges did you experience with the DMIG process? • How long did DMIG take to run for your environment? • How big (gigs/records) would you estimate your environment to be? • How many times did you run DMIG?

  6. Contra Costa Community College District • DMIG ran 3.5 days • 1 additional day was needed to defrag indexes • The size of our SQL database environment is: • SQL DB: 25 GB • SQL IDX: 135 GB • SQL LOG: 36 GB • We ran 9 tests of DMIG before the final migration to production

  7. Contra Costa Community College District • The size of our environment pushed ALL of the limits of DMIG! • We ran into MANY errors and DMIG constantly timed out • Datatel programmers and team leads were involved with every execution • Improvements were made to DMIG throughout the project

  8. Contra Costa Community College District • DMIG weird error example: Error encountered during Bulk Insert into PERSON table during processing of PERSON file. Fatal: 2627(14): Violation of PRIMARY KEY constraint 'PK_PERSON'. Cannot insert duplicate key in object 'dbo.PERSON'

  9. SQL Impact • Did the migration result in improved overall performance of Colleague, reduced performance or no noticeable change? • Has there been any noticeable impact or improvement to specific Colleague processes as a result of the migration?

  10. Contra Costa Community College District • Overall we have experienced improved performance • It’s not the great WOW we’ve had with other hardware replacements • We’re still tuning and adjusting parameters • Some processes are faster, some are slower • Depends a great deal on computed columns

  11. Marygrove College • Experienced improved performance • Overall performance better more stable • Migrating existing SQL jobs was challenging with a new domain • We’re still running some scheduled process on SQL 2005 as well as Report Manager • End user’s running jobs against SQL affects performance considerably • Looking to replicating to separate Enterprise SQL server for Report Manager and end user’s SQL access

  12. SQL Reporting • What was your approach to reporting from the Unidata environment? • What solution did you implement for SQL reporting? • How has the transition to SQL reporting been for your I.T. Department and end-users?

  13. Contra Costa Community College District • All of our prior to the migration reports were Query Builder, except a few custom Envision reports • We took advantage of our Microsoft Campus License Agreement and implemented SQL Reporting Services • At no cost • A lot of work for the I.T. Department, Users are VERY Happy • Now implementing SQL Report Builder for ad-hoc reporting • See Fred Tahan’s session at DUG!

  14. Marygrove College • Use SQL Reporting Services • At no cost • Currently in process of establishing project to migrate from SQL 2005 to SQL 2008 • All report development in the I.T. Department • Limited resources to complete • Looking at implementing SQL 2008 differently • Users are frustrated they cannot build their own reports • Also do reports from Envision and Studio • Sometimes it is just easier, better control in logic

  15. Consulting and Services • What consulting services did you purchase from Datatel to assist with the migration? • Did you purchase services from another company? • How much of the migration did you perform yourself?

  16. Contra Costa Community College District Consulting Services from Datatel: • Initial Colleague Installation – clean environment, LPR • Minimal services – some programming • Registration Health Check (after migration) Other Consulting: • Brent Ozar PLF – SQL Server Health Check • iData – QB to SQL Reports • We did all of the migration ourselves, with a LOT of help from Solution Center

  17. Marygrove College All in house – your looking at the main resource!

  18. Job Duties • How has system administration (tuning, parameters, maintenance, etc.) changed from a Unidata perspective to SQL? • Has there been an impact to staffing the System Administrator, Programmer/Analyst and other positions?

  19. Contra Costa Community College District • No more WUFA • Windows maintenance • Occasional SQL Reindexing and Defrag • No impact to Staffing • Training was conducted during the migration project

  20. Marygrove College • System administration close to the same • Patching is the same • More work since two servers compared to one • I still do a lot of my work at the : • On the rare occasion, I use SQL • System Administrator does a lot of work on SQL Staffing Impact • has skill set in SQL more so than Envision • Trying to get IT staff to adopt Envision more

  21. Lessons Learned • What would you have done differently?

  22. Aftermath • Did anyone involved in the migration experience serious physical injuries, loss of mental capacity or were otherwise impaired as a result of the migration?

  23. Contra Costa Community College District • “Daniel and I used to be friends…” • Before • During • After

  24. Questions • Questions from the audience?

  25. Thank You!

More Related