By: Jeremy Kadlec | Last Updated: 2014-10-07 | Comments | Disaster Recovery
In our SQL Server environment we have a mix of SQL Server and Windows versions not to mention a mix of applications, hardware and server locations (on site, off site, in the cloud, etc.). This makes management, disaster recovery, high availability, etc. very difficult with 24x7 demands and so many variables. We need to simplify our SQL Server and Windows management as well as get a handle on the environment in order to make smarter decisions about resource allocation, licensing and personnel management. At this point we have not had any issues my team cannot quickly fix, but I need to rein in the environment before the situation gets completely out of control and we miss our Service Level Agreements (SLAs). Do you have any suggestions?
It sounds like you are facing a multitude of issues for both SQL Server and Windows management in terms of personnel management, licensing costs, consolidation, upgrades and more. Natively, you have a few options for disaster recovery and high availability to support your 24x7 needs with the Microsoft stack including: Windows Clustering, SQL Server Availability Groups, Replication, Log Shipping, Database Mirroring and Backups. However this looks like only one piece of the puzzle; you still need to consolidate your servers, maximize your licensing, upgrade to a consistent platform and give your team time to plan for growth.
With all of these challenges, I would like to introduce you to a solution from DH2i which can help address your needs. In a nutshell, DH2i software decouples the application instance from the host OS and IT infrastructure allowing you to re-host SQL Server workloads from any host to any host anywhere—as fast as the instance can stop and restart. DH2i's robust solution can help manage SQL Server instances across your organization for disaster recovery, high availability, consolidation and upgrades. Let's see how it can help.
SQL Server High Availability and Disaster Recovery
Running 24x7 is no longer a requirement, it is assumed. Unfortunately, with a mix of physical and virtual servers, versions of SQL Server, Windows, storage, etc., the technical challenges can grow quickly. With DxConsole from DH2i, this product supports your existing environment for high availability and disaster recovery via local and remote clustering. This means you do not need to work through resource intensive application changes or building a brand new infrastructure to fulfill a business expectation that is assumed.
Here is how DH2i can help you:
- Clusters - Local or stretched across remote data centers with your existing SAN infrastructure
- Management - Manage SQL Server at an instance level from a single interface
- Windows Support - 2008+ for Standard or Enterprise edition
- SQL Server Support - 2005+ for Express to Enterprise editions
- Infrastructure - Physical, VMware and/or Hyper-V in any mix in a single cluster
Figure 1 - Sample DH2i Architecture
Flexible SQL Server Management
Managing SQL Server high availability and disaster recovery should not be difficult, time consuming or counter intuitive. With many native SQL Server solutions operating at the database level, it is sometimes difficult for everyone on the team to be able to failover as needed to continue business operations. Even for skilled SQL Server Professionals it can be challenging to drop what you are doing and begin fire fighting when an issue occurs if you do not have all of your scripts up to date. This means that the downtime can be for an extended period of time creating more pressure for you and your team as well as painting the wrong picture for your company's reputation.
Here is how DxConsole from DH2i can help you:
- Interface - Single interface to manage SQL Server instances across the organization
- Training - Simplified process to determine server status, move SQL Server instances on nodes and failover SQL Server instances
- Monitor Performance - Ability to quickly assess server performance and health with minimal training
- Connection String - Single connection string for applications regardless of the SQL Server instance location
- Confidence - Team members are able to quickly assess the situation and determine how resources should be utilized
Figure 2 - DH2i Cluster View
SQL Server Consolidation and Upgrades
Beyond SQL Server disaster recovery and high availability, looking across many environments, it is common to see a few different paradigms that are a concern:
- Server Utilization - Expensive servers idling while others are underperforming during peak operating hours
- Outdated Versions - Servers experiencing errors from service packs out of date or servers with Windows and SQL Server versions that are no longer supported
- Server Sprawl - Individual physical or virtual servers for specific applications
In all of these circumstances, the common denominator is lack of time for the Windows and SQL Server Administrators to properly manage the environment. In many respects virtual machines did not save time when numerous virtual machines still have to be patched and upgraded. It takes time to coordinate the patching, test the applications and work with the technology team to validate the business requirements are met.
With DH2i, the products are setup as clusters to manage multiple instances of SQL Server on each node of the cluster. DxConsole enables you to manage all of the servers, understand the performance of each SQL Server instance and be able to move an instance of SQL Server to another node that has the resources to support the workload. This is accomplished without the development team having to make connection string changes. This gives Windows Administrators far less servers to manage while maintaining the integrity of the SQL Server instances for the DBAs.
In terms of upgrades, the proper planning and testing is still required in QA environments to ensure the applications are meeting the business needs, but only once the testing and validation process is completed, moving to production is fairly simple. With DH2i, you are able to upgrade specific nodes in your cluster that are not supporting production instances of SQL Server. Once these nodes are upgraded, during a maintenance window you can drag and drop individual SQL Server instances from within the DxConsole and they are upgraded on the new node. This process consists of upgrading the database to the new SQL Server version and at that point you can test the application. If there is an issue the rollback is very simple. Go back to the DxConsole to drag and drop the individual SQL Server instance to the previous node with the original SQL Server version and bring a storage snapshot online right before the upgrade. At that point you can continue business operations on the previous version of Windows and SQL Server until the upgrade issue is resolved.
Figure 3 - DH2i Upgrade SQL Server Instance
SQL Server Resource Allocation and Performance
Understanding resource allocation, trending, peak usage periods, etc. across a SQL Server environment can be challenging, but necessary to understand the available capacity to support the business growth and new projects. This is also critical to ensure SQL Servers are able to perform optimally after a consolidation of instances. With DxConsole from DH2i, this tool provides insight into performance metrics at the instance level to understand peak usage among numerous SQL Server instances supported by the same host. The tool set also offers the ability to add any Performance Monitor counter to customize your monitoring. Here is a sample view of the interface.
Figure 4 - DH2i Performance Monitoring and Analysis
Beyond monitoring SQL Server instances, there are also opportunities to be proactively notified of potential issues, as shown below where the team is notified if a host's average CPU usage exceeds 90%. Based on the circumstances it is possible to configure the policy to email the team, load balance the workload, run a script or all of the above. This functionality makes it possible to notify the team and prevent issues in well defined circumstances.
Figure 5 - DH2i Performance Policy
Reduce Total Cost of Ownership for SQL Server
With the recent changes to Windows and SQL Server licensing as well as the constantly improving CPUs (i.e. increased cores per socket and processing power improvements) the traditional schools of thought for sizing SQL Servers can become very costly. Justifying the need for a single database per SQL Server instance, Enterprise Edition, Software Assurance, Administrators time to manage hundreds of severs and more can be a difficult situation. With DH2i organizations can reduce the total cost of ownership in the following ways:
- Licensing Savings - Avoid unnecessary Windows\SQL Server Enterprise and Software Assurance Licensing
- Maximize Servers Resources - Consolidate SQL Server instances on a cluster maximizing server resources
- Manage Less Servers - Reduce the time needed by Windows and SQL Server Administrators to manage and patch numerous servers
- Consolidation - Not only save time managing and money licensing, but also less power and smaller foot print in the data center
- Reduce Planned Downtime - More of the patching and upgrades can be completed during normal business hours
- Reduce Unplanned Downtime - Local and stretch clustering delivers high availability and disaster recovery to protect the business
- Leverage Existing Infrastructure - Regardless of the infrastructure, DH2i can deliver seamless high availability and disaster recovery
- As you research and explore solutions to meet your SQL Server disaster recovery and high availability needs, be sure to take a holistic look at your environment to find a solution that can meet the business written and unwritten requirements, save your company money and is a solution your team can support.
- DH2i can help you simplify SQL Server disaster recovery, high availability and management, with its flagship product DxConsole.
- Learn more about all that DH2i has to offer - http://www.dh2i.com/.
MSSQLTips.com Product Editorial sponsored by DH2i, makers of DxConsole.
Last Updated: 2014-10-07
About the author
View all my tips