Wednesday, June 24, 2009

Recently executed SQL Statements


SELECT last_execution_time , [text] AS [Statement]

FROM
sys.dm_exec_query_stats

CROSS
APPLY
sys.dm_exec_sql_text(sql_handle)

ORDER
BY last_execution_time DESC

Thursday, June 4, 2009

Failover Clustering

Failover Clustering

SQL Server 2005

  • Multi-instance clusters – each instance requires its own resources
  • Multi-node – more nodes per configuration, active/active, active/passive, N+1 – plann carefully
  • Analysis Services clusterable
  • See http://msdn.microsoft.com/en-us/library/ms143511.aspx
  • Integration Services – Can be clustered. Discussion - Where to store the packages?
  • Reporting services not clusterable  - Consider using network load balancing instead
  • Can rename a clustered instance
  • Majority Node Set
  • See http://suppot.microsoft.com/kb/838612

SQL Server 2008

  • Cluster Validation tool
  • Support for up to 16 nodes
  • New quorum model: File Server Witness, Node Majority
  • Edit subnet mask, DHCP, IPv6
  • GPT disks, support for >2TB partitions
  • Improved cluster setup, error reporting
  • Rolling upgrades, one node at a time
  • See http://msdn.microsoft.com/en-us/library/ms189910.aspx

Failure Detection

  • Node failure
  • Resource failure detected
  • LooksAlive – SQL Server status – default every 5 seconds
  • IsAlive – SELECT @@SERVERNAME – default every 60 seconds
  • See http://blogs.technet.com/rob/archive/2008/05/07/failover-clustering.aspx 
  • Upon failover – new instance comes up on other node
  • SQL Server 2005+ – Enterprise Edition – service available after Redo phase completes
  • Failback – Available using use preferred owner

Troubleshooting

  • Verify failover
  • Careful – Post-install tasks like manual IP ports, additional disks – Check resources
  • Logs: Windows Event Logs, Cluster log, SQL Server Setup log
  • Books Online: Failover Cluster Troubleshooting
  • See http://msdn.microsoft.com/en-us/library/ms189117.aspx

Clustering Details

  • Client design: To the app, failover process looks like server taking a long while to respond. Consider adding retry logic.
  • Planning: Verify hardware solution, run validation, plan security for service accounts, plan SQL Tools location
  • Migration: Verify OS settings (CSP, Kerberos), MSDTC, pre-requisites
  • Heartbeat: TCP/IP configuration, binding order, remove NETBIOS
  • Virtualization: Check support policy (KB below), guest failover not supported, SVVP
  • See http://support.microsoft.com/KB/956893
  • See http://support.microsoft.com/KB/956893
  • Adding a node: Set SQL setup, select add cluster node
  • Removing a node: Set SQL setup, select remove cluster node
  • SQL Server setup can be fully scripted, including cluster install, add cluster node, etc.
  • Example: SETUP.EXE /q /ACTION=InstallFailoverCluster /INSTANCENAME=...
  • Example: SETUP.EXE /q /ACTION=AddNode /INSTANCENAME=...
  • See http://msdn.microsoft.com/en-us/library/ms144259.aspx 
  • Careful – SQL Server 2008 cluster setup is now run one node at a time
  • Cluster log: Use CLUSTER.EXE command line to obtain text log
  • See http://blogs.msdn.com/clustering/archive/2008/09/24/8962934.aspx

Combining with other features

Clustering vs. Mirroring


 

SQL Server 2008 Database Mirroring

Database Mirroring

  • Software solution for high availability.
  • Per database and not per server.
  • Increase data protection, availability, upgrade availability.
  • Careful – A lot of things live outside the database
  • SQL HA options for the masses, without the high-end hardware requirement.
  • Very popular option.
  • See http://msdn.microsoft.com/en-us/library/ms189852.aspx

Requirements and Limitations

  • Full recovery model
  • Express (witness only), Standard (can't do async) or Enterprise
  • Logins (logins are not in the database, you need to make sure they are on the other side)
  • Cannot have multiple mirrors – use log shipping if you need this
  • Not intended for a large number of databases – Depends on traffic
  • Careful – In multi-database applications, failover behavior
  • Cannot mirror master, msdb, tempdb, model

Endpoints

Modes

Clients

  • Clients: ADO.NET v2 required for automatic failover: Failover partner in connection string.
  • Careful – Client will ask the primary upon connect. Can try the partner if primary not there.
  • In SQL Server 2008: Failover partner cached in registry upon first connect to primary (no change to connection string required if primary available on first connect)
  • See http://msdn.microsoft.com/en-us/library/ms366348.aspx

Reporting on Mirror

  • Mirror can be access as read-only.
  • Database snapshots can be created on mirror, you can use snapshot for reports.
  • See http://msdn.microsoft.com/en-us/library/ms175511.aspx
  • Snapshot cannot be restored to mirror. Snapshot performance implications.
  • Careful – refreshing snapshot, finding the correct snapshot, failover situations
  • Consider using snapshot as source for a separate reporting database.
  • Consider having a process that keeps track of which is the latest snapshot.

Clustering and Mirroring

SQL Server 2008

Failover

Upgrade Steps

  • Switch to high safety (sync). Make sure it's in sync.
  • Disable the witness.
  • Perform a rolling upgrade (upgrade mirror, manual failover to mirror, upgrade original principal, manual failover to original principal).
  • Switch back to high performance (async)
  • Re-enable the witness.
  • See http://msdn.microsoft.com/en-us/library/bb677181.aspx
  • Careful – Some data loss may occur

TechNet Best practices

Performance counters

Failover process

  • Failover occurs
  • Some time to detect the failure (hard failures (network outage) are quicker than soft failures)
  • Some time to coordinate with the witness
  • Decision to failover
  • Some time (order of a second) to actually fail over
  • Database available on new principal
  • Database Undo continues
  • Time to failover typically just a few seconds (varies depending on case – pull network cable, power off principal, stop sql server service, shutdown principal, manual failover)

Network

Automatic page repair

Demo

  • Database in full recovery mode
  • Backup original database
  • Restore both backups with NO RECOVERY
  • Careful – Consider options to migrate and keep other objects in sync (Logins, SQL Server Agent jobs (disabled), SSIS packages, linked servers, backup devices, maintenance plans, database mail profiles, etc.)
  • In SSMS, Database "Recovering…", click on Tasks, Mirror…
  • Witness – can be enabled from principal or partner, but keep in separate server
  • Principal – Listener port, encryption, endpoint
  • Partner – Listener port, encryption, endpoint
  • Security – Service accounts for principal, partner
  • Review screen – Principal and Mirror endpoints – format is TCP://computer:port
  • Option to start mirroring right away…
  • SELECT * FROM sys.endpoints
  • SELECT * FROM sys.dm_db_mirroring_connections
  • Notice that the wizard made a few calls on your behald, like encryption protocol
  • Careful – If status is "Synchronizing…" all the time, you're running behind (not keeping up)
  • Adding a witness
  • Launching "Database Mirroring Monitor"

Demo with TDE 

Mirroring Details

  • Backup: Can't backup mirror, can't backup snapshot, Consider log shipping for those
  • Monitoring: Check if partner is keeping up
  • Monitoring GUI: GUI tool, dm_monitor monitor role
  • Monitoring SP:  sp_dbmonitorupdate, once per minute by default, updates internal msdb table
  • See http://msdn.microsoft.com/en-us/library/ms403827.aspx
  • States: Synchronized, Synchronizing, Suspended (pause),  Disconnected
  • See http://msdn.microsoft.com/en-us/library/ms189284.aspx
  • Cross domain security: Use certificate security
  • See http://msdn.microsoft.com/en-us/library/ms191140.aspx
  • Client design: Consider the added latency when working with high safety
  • Network issues: No set limits for async, compression helps, consider the consequences
  • Currently no support for combining filestream and mirroring (consider Clustering)
  • Careful - Mirroring and multi-dabase apps. Mirror is per database.

Mirror and other technologies

  • Mirroring and Replication – Supported
  • Mirroring and Database Snapshots – Supported
  • Mirroring and Clustering – Supported. Typical: Cluster local, async mirroring to remote
  • Mirroring and Log Shipping – Supported. Consider carefully
  • See http://msdn.microsoft.com/en-us/library/bb500117.aspx

Related blog posts