HyperV and SQL Administration Presented by Kevin Brunson Chief Technology Officer What We Will Discuss Microsoft HyperV What is it Common Tasks Demo HowTo Enable Replication Failover a VM Enable Live Migration ID: 767084
Download Presentation The PPT/PDF document "Hyper-V and SQL Administration" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Hyper-V and SQL Administration Presented by: Kevin BrunsonChief Technology Officer
What We Will Discuss Microsoft Hyper-V What is it?Common Tasks Demo: How-ToEnable ReplicationFailover a VMEnable Live MigrationGotchas
What We Will Discuss Windows 2016 Hyper-V Crystal BallWhat do we know?Confirmed in Technical PreviewWhat do we think?Discussed by Microsoft
What We Will Discuss SQL AdministrationSQL Physical DB Architecture ReviewSQL Recovery ModelsSQL Backups/Maintenance PlansTips/Tricks/Regular Tasks
Why The Broad Range of TopicsI couldn’t make up my mind I don’t want to rehash the same thing over and overJason Powell said “Do it all”
Hyper-V in a Nutshell
Host/Guest Model Host ServerWindows installed on hardware like traditional OSRuns Hyper-Visor softwareGuest ServerWindows installed on software masquerading as hardwareRuns applications
Common TasksReplication/FailoverLive Migration Go and Do Likewise
Hyper-V Replication Concepts How does it work?Server configuration replicated onceDrives replicated on a regular basisFailover/FailbackHow much bandwidth does it take?I hate to answer a question with a question…How much data are you trying to keep replicated?How fast does it change?Web Server – Probably doesn’t change much Exchange Server – Probably changes a lot
Hyper-V Replication ConceptsWhat is it for? Very fast recovery of server failureSite redundancy if you have the bandwidthWhat is it not for?Backups
Demonstration – Live Migration Let’s Move a Server With No DowntimeA few things to noticeHyper-V doesn’t compress the drive contents when performing a Live Migration, so it takes longer than ReplicationYou can’t Live Migrate a server if the server has already been replicated to the destination hostYou will need to make some changes to Active Directory before Live Migration will work correctly DEMO
Demonstration - Replication Let’s Enable ReplicationA few things to noticeHyper-V compresses the contents of the disks as they traverse the network.Once the system is replicated, you can change settings on the primary system and it won’t update the replica.DEMO
Demonstration - Failover Now Let’s Failover a ServerA few things to noticeFailover requires more than just starting the server, but not muchA Planned Failover is graceful and resyncs changes before it completes. An Unplanned Failover is not graceful and assumes the data on the old server is worthless.DEMO
GotchasCheck replication health on a regular basis Domain controllers are not good candidates for replication
What’s Coming?Features in Technical PreviewFeatures Rumored for Final Release Hyper-V In The Future
Features that Improve Administration Almost instant VHDX creation, even for Fixed Size DisksFixed size VHDX in less than 5 seconds, no matter the sizeAdd/Remove RAM on a running VM, even if you’re not using Dynamic MemoryAdd/Remove Network Adapters on a running VMIntegration Services drivers deployed via Windows UpdatePowershell Direct
Features that ImproveSecurity or Reliability Nano92% fewer critical bulletins, 80% fewer rebootsVM Checkpoints use VSS technology in the VM instead of Saved StatesApplication consistent snapshotsShielded VMsBitlocker encrypted VHDX, virtual TPMHost Administrator can have limited or no access to VM
Features that We May or May Not See Nested VirtualizationMicrosoft says it will be there at releaseNot supported in TP3Why does it matter?GUI out of the boxTP2 required you to enable GUI after installTP3 gives an install option during OS loadWhere will they end up?
A Short Biblical Study One of the most commonly used names of God in the Old Testament, used almost 300 times, is Jehovah Sabaoth, which meansLord of Hosts.
God Is The Ultimate VirtAdmin
Reset and a Prayer Prayer of the Broken VMPsalm 80:19 (ESV)Restore us, O Lord God of hosts!Let your face shine, that we may be saved!
Now the Awkward Transition
SQL Physical DB Architecture ReviewSQL Recovery ModelsSQL Backups/Maintenance Plans Tips/Tricks/Regular TasksSQL Fundamentals
SQL Database Files SQL Databases are composed of at least 2 files MDF - Contains schema and dataLDF - Contains a history of changes made to the databaseBoth are required for a database to function
Database Recovery Models FullAll transactions recorded in transaction logSimpleSystem reclaims log space as necessaryBulk-loggedUses minimal logging for bulk transactions
Full Recovery Model Allows point-in-time restoresLDF file growth can become a problem without proper maintenanceRestore process is more complicatedGreatest system resource overhead
Simple Recovery Model Can be restored only to time of last full backupLDF file remains smallRestore process is simpleSmallest system resource overhead
Bulk-logged Recovery Model Not point-in-time, but can be restored to time of last backupLDF file remains smaller than Full RecoveryNot recommended for production systemsThat will be the last time we mention Bulk-logged
Backup TypesFull Backup Complete copy of your databaseIncludes MDF and LDFTransaction Log BackupDump of the database transaction logContains all changes to the database since the last log backup
Full BackupRestorable by itself or as part of a point-in-time restore process Can function as the start of a transaction log chain for point-in-time restores
Transaction Log BackupUsed only for point-in-time restores Used only for Full Recovery databasesMust be used in conjunction with a full backupEntire transaction log chain must be present
What Happens When?Full Backup Makes no changes to the databaseTransaction Log BackupMarks transaction log as overwriteableA large LDF file will still be large, but it will be essentially emptyWon’t grow again until the file fills up
Walk Through The Backup Lifecycle First Full Backup Allows DB to start loggingTransaction Log Backup startsSQL dumps contents of log to backup locationSQL marks log as not neededSQL starts overwriting logServer runs out of spaceSQL stops servicing
Backup Considerations Backup CompressionSQL 2008 Enterprise2008 R2, 2012, 2014, Standard and EnterpriseConsiderably less disk spaceFaster backup performanceTake the performance hit into consideration when schedulingTransaction Log Backups should occur as often as necessary to meet business requirementsLocal backups don’t help if you lose the volume
Why Don’t I Have Backups?SQL Express doesn’t come with the SQL Agent If you have Express you will need to run backups another way3rd Party Backup Application (Like Shelby Backup/Restore) for schedulingJobs run via batch file from Scheduled TasksManualYou probably shouldn’t use Full Recovery on an Express database
What is SQL Express anyway? SQL Express is designed for small databases with small user countsSQL 2014 Express has the following limits:10GB MDF file size1GB RAM per instance1 socket or 4 coresFor comparison, SQL Standard can use:524PB MDF file size128GB of RAM per instanceThe lesser of 4 sockets or 16 cores
Other Key MaintenanceCleanup – Delete ol d files by ageMake sure you take bak and trn into accountShrink – Remove unused pages to reduce filesizeIf in Full Recovery, run Transaction Log backup firstRe-index – Speed up your DBs by reducing index fragmentation Causes a big spike in Transaction Log size
Weekly (Or Daily) Tasks for You Check Disk SpaceCheck DB Size (MDF and LDF)Agent Job Status Are Full Backups running?Are Transaction Log Backups running?Are other Maintenance Plans running?Verify that your backups are writing to something else: Tape, Disk, Offsite, External Storage, etc.
Monthly (Or Weekly) Tasks for You Test RestoreAre your backups actually usable?Windows UpdatesSQL Updates actually fix real problemsCheck DB Size (MDF and LDF)Agent Job Status Are Full Backups running?Are Transaction Log Backups running?Are other Maintenance Plans running?Verify that your backups are writing to something else: Tape, Disk, Offsite, External Storage, etc.
About the Presenter: Kevin Brunson is the Chief Technology Officer of Higher Ground Technologies in Collierville, TN. He holds a BA degree from Crichton College and a myriad of industry certifications including MCSE. Kevin has been working with church technology since 2000.
Higher Ground Technologies, Inc exists to serve the unique technology needs of churches nationwide. The company provides network design and consulting services, system integration and a full suite of IT management services for churches wishing to outsource some or all of their technology management. Higher Ground Technologies also provides VoIP communication systems, enterprise wireless networks, secure online backup services, 24x7 network monitoring and support, and high performance, extremely reliable network servers and security devices.