1 / 25

Clustering SQL Server

Clustering SQL Server. Tom Pullen Senior DBA, RM Education tpullen@rm.com. Agenda. Why Cluster? Is it worth the hassle and expense? How does it compare to other HA technologies? How to Cluster plan prepare execute test/monitor. Why Cluster?.

sebastian-ramirez
Download Presentation

Clustering SQL Server

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. Clustering SQL Server Tom Pullen Senior DBA, RM Educationtpullen@rm.com

  2. Agenda Why Cluster?Is it worth the hassle and expense?How does it compare to other HA technologies? How to Clusterplanprepareexecutetest/monitor

  3. Why Cluster? High availability requirements – 24X7, patching, hotfixes Cost and Benefit Requirements: hardware, software, licenses Alternatives: comparing clustering with other HA technologies Limitations – what does clustering not protect you from? Combining clustering with other HA technologies – log shipping, mirroring, offloading reporting load

  4. How to cluster Plan Prepare Execute Test

  5. Plan • Consult your users/boss/finance director! • Design your cluster: Nodes, Instances, Storage • Write a plan for build • Write a plan for migration/go-live • Think of everything! • Get your plans and design peer-reviewed • Think about failure – how to go back? • Plan decision points – when/if to proceed, or give up & the points of no return

  6. Prepare Do everything you can in advance- create security accounts - obtain/reserve IP addresses- obtain install media – and SP install files- ensure you accurately estimate the time requirements – be conservative!- plan downtime, advertise downtime, arrange holding pages- think about all the other things you normally need on a new server: logins, jobs, configs, SPs, backup software, backup location, tapes?, database mail, alerts etc!

  7. Execute Print your plan Follow it Mark your progress Give yourself incentives! Publicise your progress Get help if you encounter problems Be willing to give up if necessary

  8. Steps in clustering Build servers, OS, AD, networking, SAN/LUN presentation [or have this done for you] – check everything! Install/configure MSDTC Install clustering Create cluster groups Cluster MSDTC Test failover Install SQL Server instance(s) Service Pack/Hotfix/Cumulative Update(s) Configure Do all the other normal things! (Jobs, logins etc) Test failover and failure

  9. MSDTC MSDTC – Under Windows 2003, it is a pain MSKB docs can be misleadingHow to enable network DTChttp://support.microsoft.com/kb/817064How to cluster MSDTChttp://support.microsoft.com/kb/301600 Enable network DTC on each node and set permissions BEFORE clustering Under Windows 2008/SQL Server 2008, things are much improved! If you get into a tangle clustering MSDTC, it is sometimes quicker to strip everything out (cluster .. /forcecleanup) and start again! Clustering itself is easier than getting MSDTC right!

  10. Cluster! Shut down all but the first node Cluster Administrator → new cluster Enter name, IP, Quorum disk Switch on other server(s) and add them to the cluster Configure heartbeat network Create cluster groups and disk resources ready for SQL Server to install into

  11. Cluster Administrator

  12. A group before SQL Server is installed

  13. cluster.exe command example CLUSTER /CLUSTER:SQLCLUS01 GROUP "BACKUP1" /CREATECLUSTER /CLUSTER:SQLCLUS01 GROUP "BACKUP1" /ONLINECLUSTER /CLUSTER:SQLCLUS01 GROUP "BACKUP1" /SETOWNERS:SQL05CLUSTER /CLUSTER:SQLCLUS01 RESOURCE "LUN U_BAK" /CREATE /GROUP:"BAK_SHARE" /TYPE:"Physical Disk" /PRIV DRIVE="U:" /OFFLINECLUSTER /CLUSTER:SQLCLUS01 RESOURCE "LUN U_BAK" /ONLINECLUSTER /CLUSTER:SQLCLUS01 RESOURCE "Backup IP Address Public" /CREATE /GROUP:"BACKUP1" /TYPE:"IP Address" /PRIV ADDRESS="194.168.190.44" /PRIV SUBNETMASK="255.255.255.0" /PRIV NETWORK="Public" /OFFLINECLUSTER /CLUSTER:SQLCLUS01 RESOURCE "Backup Network Name Public" /CREATE /GROUP:"BACKUP1" /TYPE:"Network Name" /PRIV NAME="SQLCLUS01BAK" /OFFLINECLUSTER /CLUSTER:SQLCLUS01 RESOURCE "Backup Network Name Public" /ADDDEPENDENCY:"Backup IP Address Public"CLUSTER /CLUSTER:SQLCLUS01 RESOURCE "Backup Network Name Public" /PRIV RequireKerberos=0CLUSTER /CLUSTER:SQLCLUS01 RESOURCE "Backup_Share" /CREATE /GROUP:"BACKUP1" /TYPE:"File Share" /PRIV SHARENAME="BACKUPS1" /PRIV PATH="U:\" /OFFLINECLUSTER /CLUSTER:SQLCLUS01 RESOURCE "Backup_Share" /ADDDEPENDENCY:"Backup Network Name Public"CLUSTER /CLUSTER:SQLCLUS01 RESOURCE "Backup_Share" /ADDDEPENDENCY:"LUN U_BAK"CLUSTER /CLUSTER:SQLCLUS01 RESOURCE "Backup_Share" /PRIV Security="Everyone",set,C:securityCLUSTER /CLUSTER:SQLCLUS01 GROUP "BACKUP1" /OFFLINECLUSTER /CLUSTER:SQLCLUS01 GROUP "BACKUP1" /ONLINE

  14. Install SQL Server Instance(s) Run setup! Do you like unattended installs/batch files? SQL Server 2005 runs multiple times automatically on each node using Task Scheduler SQL Server 2008 must be installed on each node Interactive install of SQL Server requires you to specify:- Components to installInstance Name, Virtual Server Name + IP, Cluster Group, Cluster Node ConfigurationSetup account infoService accountDomain group for cluster servicesAuthentication modeCollationError usage & reporting Unattended install requires you to specify all of these in the config file Do it all over again for each instance! Service pack – does each engine instance on each node. Must be run from active node. Don’t forget to Service Pack Tools, Shared Components, SSIS, SSNAC/connectivity, etc

  15. Install SQL Server Instance(s)

  16. Now make your instance like you normally would! Configure! RAM especially. Think about min, max, what can be where?MAXDOP? Max worker threads? Logins Jobs Alerts Database Mail SPs in system DBs? Startup SPs Trace Flags Auditing Maintenance Sysmessages? Static data? Resize system DBs? Move/split tempdb files? Any other things that are particular to your environment

  17. Test! Test failover Test failure Fix issues Run each instance on every Node it can exist on and check it is ok

  18. Migrate Get your User DBs on – migrate or create? Populate (Re)point your apps Test your apps Anything else? (Replication?) Reporting services? Analysis Services?

  19. Monitor Enable alerts Failover – startup SP? Monitor performance Monitor availablility Monitor disk space Monitor your monitor!

  20. Some real-world clusters

  21. Some real-world clusters

  22. Some real-world clusters

  23. Some real-world clusters

  24. Summary Failing to plan is planning to fail Write your plan – and follow it! Don’t be scared of clustering Know where to find help Practice if you can with test servers Don’t panic! But feel free to curse MSDTC...

  25. Good Resources How to cluster Windows 2003 http://www.sql-server-performance.com/articles/clustering/cluster_server_2003_p1.aspx How to cluster SQL Server 2005 http://www.sql-server-performance.com/articles/clustering/cluster_sql_server_2005_p1.aspx MSDN Clustering Blog http://blogs.msdn.com/clustering/ Windows 2008 Clustering Step-By-Step http://technet.microsoft.com/en-us/library/bb727114.aspx Windows 2003 Microsoft White Paper (comprehensive!) http://www.microsoft.com/downloads/details.aspx?familyid=96F76ED7-9634-4300-9159-89638F4B4EF7&displaylang=en

More Related