1 / 35

New Data Management Capabilities with SQL Server 2012 over NAS

New Data Management Capabilities with SQL Server 2012 over NAS. Pat Sinthusan Reference Architect pat.sinthusan@netapp.com. Protocols. Fibre Channel (FC) Fibre Channel over Ethernet ( FCoE ) Internet Small Computer System Interface ( iSCSI ) Network File System (NFS)

stacia
Download Presentation

New Data Management Capabilities with SQL Server 2012 over NAS

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. New Data Management Capabilities with SQL Server 2012 over NAS Pat Sinthusan Reference Architect pat.sinthusan@netapp.com

  2. Protocols • Fibre Channel (FC) • Fibre Channel over Ethernet (FCoE) • Internet Small Computer System Interface (iSCSI) • Network File System (NFS) • Server Message Block (SMB)

  3. Protocols for Physical Host

  4. Protocols for Windows Guest VM Network stack Hypervisor IO stack

  5. SQL Server Implementation Protocols

  6. Storage Protocol Comparison for SQL Server • NetApp provides host software integration for all protocols to make it easy to implement

  7. Storage Management Challenges • Need for simpler storage provisioning • Need to migrate databases across hosts and storage systems • Cross-team dependencies for storage provisioning slow us down • Reduce operational costs for SQL Server deployments

  8. Create LUN Figure out your WWN Zone FC switch Map iGROUP Present LUN Format LUN with NTFS Simplicity: SAN vs. NAS Provision LUN (FCP) Provision Share • Create share • Assign permissions

  9. Benefits of using SQL Server over SMB • Storage provisioning is simplified • Plan for the future with thinly-provisioned volumes • Storage manageability is streamlined • Enables end-user self service • No special equipment needed • Lower operational costs

  10. SQL Server 2012 over SMB • Allows creation of system databases on SMB shares directly at the time of installation • System databases reside on SMB shares • Supports SMB 2.x and SMB 3.0 • Allows user databases to be put on SMB shares without the use of any trace flags • SMB 3.0: • Reduced network traffic • Increased resilience

  11. Install SQL Server over SMB on Windows host • Security requirement • SQL Server Installation process System databases User databases

  12. Installation of SQL Server over SMB on NetApp Storage • Security requirement • DataOntap 8.1.2 • Installation account must be added to manage audit and security log of Group Policy • Controller must be added to the Group Policy object • SQL Server installation process • Specify Service account which regular domain account • Specify location of system database as a CIFs share

  13. Installation of SQL Server over SMB on NetAppStorage (cont) • Domain setup • Create a new Organization Unit (OU) or group • Add controller to the new OU that just been created • Add SQL Service account to be able to Manage auditing and security log

  14. Install SQL Server over SMB (cont)

  15. Install SQL Server over SMB (cont) • Controller setup • CIFs Group Policy Object must be enable (options cifs.gpo.enable on) • Add installer and SQL Server service account to the share

  16. Install SQL Server over SMB (cont)

  17. Caveats and Workaround • Creating a SQL Server Database Snapshot (NOT A NETAPP SNAPSHOT) (KB 2017128) • Snapshot file must be resided on block storage • DBCC CHECKDB (KB 2017129) • Set database to read only • Create SQL Server Database Snapshot that have snapshot file resides on the block storage • Performance Challenges • No Multiple Channels • No Remote Direct Memory Access (RDMA)

  18. Current status of SMB and NetApp storage • ONTAP 8.2 will support the majority of SMB 3 functions • After ONTAP 8.3 will support Multi Channels and RDMA • SnapDriveNextGen will support SMB • SMSQL support for SMB (post 6.0) • Enable SMB2 signing to access CIFs shares on ONTAP 8.1.1 7-mode from Windows 2012 • options cifs.smb2.signing.required on

  19. SQL Server over SMB Reccomendations • Non production environments (Development, QA and Test) • Easy to be build and deploy • Easy to manage from the storage perspective • Does not require databases to be quiesced • No special hardware and software needed

  20. Demo Installation of SQL Server 2012 over SMB • Installer is sea-tm\_DBA • SQL Server Service Account is sea-tm\_SQLAdmin • CIFs share is \\SQL12SMB2\MSSQL

  21. Has this ever happen to you? • How long would it take to backup 1.25 TB database? • How long would it take to restore 1.25 TB database?

  22. Take Advantage of SQL Server database on NetApp Storage • Simple Recovery backups with Snapshot of SMB share • SMB share can be cloned from the snapshot to create database of other environments • Database can be easily attached from the files over SMB share

  23. Demo of Clone database on SMB shared on NetApp Storage • Source database (1.25 TB) is in SQL12SMB3 • Database reside on SMB share • 1.25 TB database can be cloned < 1 minute

  24. SQL Server on VMware • Windows Guest on VMware ESX supports four types of storage configurations • Raw Device Mappings (RDMs) • VMware Virtual Machine File System (VMFS) datastores • Network-attached storage (NAS) datastores (NFS datastores) • SMB/CIFS

  25. Common Issues • When you select a database that resides on a VMDK, SnapManager automatically selects all the databases that reside on the VMDKs • The SnapVault option in the Verification Settings dialog box is not available for VMDK disks • The backup created on the VMDK cannot be verified on a remote physical server • Number of data stores needs to be updated when going past 8

  26. Pros/Cons of SQL Server on VMDKs • Pros • Completely virtualized solution • Pure NFS deployments possible • Cons • SnapShot occurs at Volume level so potentially larger snapshots • NFS restores are done with File Level SnapRestore • Block restores are done with Mount/Copy which can cause large snapshots • SnapMirroring can become more complicated

  27. SQL Server on NFS datastore • The following software can be used to assist in configuring storage: • NetApp Virtual Storage Console (VSC) for VMware - create datastore (NFS or VMFS) • NetAppSnapDrive for Windows (SDW) – create RDM’s • vSphere Client to create and attach VMDK

  28. SQL Server over NFS datastore

  29. SMSQL Operation with VMDKs Backup operation with SMSQL on a virtual server with VMDKs • SMSQL quieces the Database • SMSQL calls SMVI to create backup of containing Volume • SMVI Triggers the creation of the Snapshot on the containing volume • Restore operation with SMSQL on a virtual server with VMDKs • SMSQL requests a VMDK restore from SMVI • SMVI performs a VDMK level restore • SMSQL reconnects the DB and restarts SMSQL

  30. Demo – SQL Server over NFS • Create NFS datastore thru VCenter and VSC • Create VMDK thru setting from VCenter • Online LUNs and initialize them with Disk Manager in Server Manager

  31. Summary • SQL Server 2012 now supports all NAS protocols (SMB and NFS) • Enables end-user self service • No special equipment needed • Lower operational costs

  32. References • http://www.vmware.com/files/pdf/techpaper/Storage_Protocol_Comparison.pdf • http://web.netapp.com/MailArchives/archive/dl-server-virtualization/2012/02/0041.html • https://kb.netapp.com/support/index?page=content&id=3012688 • http://sharepoint.corp.netapp.com/sites/teso/fpeng/arb/MA/MA%20Modules/Module%20-%20Microsoft%20SQL%20Server%202012%20on%20VMDK/Module%20-%20Microsoft%20SQL%20Server%202012%20PP%20on%20VMDK%20-%20Provisioning%20Procedures.docx • https://library.netapp.com/ecmdocs/ECMP1120486/html/GUID-CF67035E-E433-4CFE-AAB5-59785F85E10F.html • SnapManager® 6.0 for Microsoft® SQL Server® Installation and Administration Guide

  33. pat.sinthusan@netapp.com NetApp Confidential - Internal Use Only

More Related