Hyper-V and SQL Administration - PowerPoint Presentation

Slide1

Hyper-V and SQL Administration

Presented by: Kevin BrunsonChief Technology OfficerSlide2

What We Will Discuss

Microsoft Hyper-V What is it?Common Tasks Demo: How-ToEnable ReplicationFailover a VMEnable Live MigrationGotchasSlide3

What We Will Discuss

Windows 2016 Hyper-V Crystal BallWhat do we know?Confirmed in Technical PreviewWhat do we think?Discussed by MicrosoftSlide4

What We Will Discuss

SQL AdministrationSQL Physical DB Architecture ReviewSQL Recovery ModelsSQL Backups/Maintenance PlansTips/Tricks/Regular TasksSlide5

Why The Broad Range of Topics

I couldn’t make up my mindI don’t want to rehash the same thing over and overJason Powell said “Do it all”Slide6

Hyper-V in a NutshellSlide7

Host/Guest Model

Host ServerWindows installed on hardware like traditional OSRuns Hyper-Visor softwareGuest ServerWindows installed on software masquerading as hardwareRuns applicationsSlide8

Common TasksReplication/FailoverLive Migration

Go and Do LikewiseSlide9

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 lotSlide10

Hyper-V Replication ConceptsWhat is it for?

Very fast recovery of server failureSite redundancy if you have the bandwidthWhat is it not for?BackupsSlide11

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

DEMOSlide12

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.DEMOSlide13

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.DEMOSlide14

GotchasCheck replication health on a regular basis

Domain controllers are not good candidates for replicationSlide15

What’s Coming?Features in Technical PreviewFeatures Rumored for Final Release

Hyper-V In The FutureSlide16

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 DirectSlide17

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 VMSlide18

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?Slide19

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. Slide20

God Is The Ultimate VirtAdminSlide21

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! Slide22

Now the Awkward TransitionSlide23

SQL Physical DB Architecture ReviewSQL Recovery ModelsSQL Backups/Maintenance Plans

Tips/Tricks/Regular TasksSQL FundamentalsSlide24

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 functionSlide25

Database Recovery Models

FullAll transactions recorded in transaction logSimpleSystem reclaims log space as necessaryBulk-loggedUses minimal logging for bulk transactionsSlide26

Full Recovery Model

Allows point-in-time restoresLDF file growth can become a problem without proper maintenanceRestore process is more complicatedGreatest system resource overheadSlide27

Simple Recovery Model

Can be restored only to time of last full backupLDF file remains smallRestore process is simpleSmallest system resource overheadSlide28

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-loggedSlide29

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 backupSlide30

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 restoresSlide31

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 presentSlide32

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 upSlide33

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 servicingSlide34

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 volumeSlide35

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 databaseSlide36

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 coresSlide37

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 sizeSlide38

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.Slide39

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.Slide40

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. Slide41

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.