280 likes | 568 Views
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.
E N D
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 • 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
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
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?
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
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
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'
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?
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
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
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?
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!
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
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?
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
Marygrove College All in house – your looking at the main resource!
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?
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
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
Lessons Learned • What would you have done differently?
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?
Contra Costa Community College District • “Daniel and I used to be friends…” • Before • During • After
Questions • Questions from the audience?