Episode 1: Data Management Challenges
Jane and Joe uncover ways to reduce database management costs. Follow their trails to learn how.
Jane identifies an issue with one of their data sources. It was running under extreme performance bottlenecks. Joe fixes a few issues while Jane focuses on the bigger picture. The system, however, has a different opinion. Find out how did Jane and Joe handle some of the most critical Data Management challenges.
Day 1 - Trouble in the Data Center
Jane is nervous as there is a problem with one of the SQL Server instances
A report requested by Management Reporting Division needed to process millions of records and caused the server's resources to be fully utilized. Joe had to kill the report's process to recover the server.
Jane received a call that made her nervous. The caller informed her that one of the business-critical applications was suffering from performance issues. The caller identified that the main cause of the performance issues was the back-end SQL Server. This was the fourth time in as many months that this issue was raised to Jane's attention and she was about to lose her temper. She calls Joe and asks him to join her in her office for a discussion.
Jane briefed Joe on the issue and informed him that her real challenge is that she cannot identify the root cause of the problem and hence, cannot possibly find a suitable solution. This system was not acquired during her tenure at the company. She had inherited the system with all its issues from her predecessor.
Luckily, Joe had run the SQL Trace tool in order to help identify the root cause of the problem. However, when Joe attempted to connect to the SQL Server instance, the connection timed out, as the system resources were fully utilized. Joe knew he cannot just restart the server, as this may cause loss of critical data.
Joe opted to use the Diagnostic Connection for Database Administrators to connect to the SQL Server instance. He collected the SQL Trace results and was able to identify the problem. While most of the users were performing transactions, someone from Management Reporting decided to run a report that needed to process millions of records. The capacity of the server was fully utilized and there was no way the server could respond to more queries.
A report requested by Management Reporting Division needed to process millions of records and caused the server's resources to be fully utilized. Joe had to kill the report's process to recover the server.
Joe returns to Jane's office with a plan
Joe ran the Database Engine Tuning Advisor. The tool suggested creating a few indexes. The query was now performing significantly better and was not consuming too many system resources.
Management Reporting had inadvertently caused the system to stop responding. This was enough motivation for Joe to dig further into the problem. Joe captured the T-SQL statement that was causing the query to run slow using SQL Profiler. He then used the Execution Plan viewer to identify reasons for the slow performance. Having other doubts, Joe decided to run the Database Engine Tuning Advisor. The Tuning Advisor suggested creating a few indexes. Joe ran the recommendations and tested the query. It was now performing significantly better and was not consuming too many system resources.
Joe returned to Jane's office and informed her of the progress he made. Yet again, Joe could not deny that the issue may strike once again in the future. He had a plan and needed Jane's approval to proceed.
Using Resource Governor, Joe made sure that long-running queries would not hamper the server's performance. Instead, priority was given to the transactional operations.
Joe decided to limit SQL Server's database engine memory to about 80% of the available RAM on the server. This would allow the Operating System and possibly some other resources to utilize the rest of the available RAM. This would mean that the server would be available for maintenance or administration even during its peak usage. Joe also decided to use the Resource Governor to limit the amount of resources that could be utilized by groups of users. This way, Management Reporting would only consume a fraction of the available resources, providing the rest of the resources to the transactional operations.
Using Maintenance Plans, Joe automated mundane daily operations tasks, such as taking backups, truncating the Transaction Logs and rebuilding indexes.
Joe also realized that the size of the Transaction Log of many databases was increasing exponentially. Furthermore, indexes needed to be updated frequently and there were no backups taken. Joe suggested using Maintenance Plans in order to automate mundane daily operations tasks, such as rebuilding indexes, taking backups and truncating the Transaction Logs. The Maintenance Plan would run after midnight at a low-traffic time and would complete prior to any user traffic.
Jane was satisfied with the outcome thus far. However, she agreed with Joe's concern. The problems may arise once again in the future. She knew they needed to do much more to prevent the issues from happening again.
Day 2 - Jane kick-starts a project
Let's make it happen!
Jane arrived at her office excited this morning. She figured there are ways to overcome some of the dramatic issues her company was facing with SQL Server. After her morning coffee, Jane walks to Joe's office and states her thoughts.
The current SQL Server farm was growing as new servers sprawled their way into the data center. This was causing management problems. If they would decide to consolidate these servers into a fewer number of machines, some of these management problems could be resolved, however, Jane was not too sure this could work. She would ask Joe for assistance on SQL Server Consolidation.
None of the servers was highly available. This meant that troubles could arise any time. Many of these servers needed to be up-and-running most of the time. This implied that patches and updates were not applied to these server in order not to introduce any downtime. Jane and Joe agreed this was not according to best practices.
Joe is back with the assessment
Joe plans to reduce the number of servers to two by consolidating SQL Server instances using multiple instances. The new servers use Always-On Availability Groups for high availability, making the management of these servers much easier.
Joe performed an assessment using SQL Server Management Studio and other tools. Joe found that there were, in total, eleven SQL Server instances running in the Data Center. Based on the processing power, Collation Settings, SQL Server Services required and high availability requirements, he could group these servers into 4 different groups. Joe suggested acquiring two large servers that have adequate processing power to cover all SQL Server instances and an additional 20% increase. Joe's plan was to install SQL Server on these servers using Multiple Instances and make the services highly available using Always-On Availability Groups. This would address the high availability requirements and help reduce the number of servers that IT professionals need to maintain.
Jane wants more. So does her business!
SQL Server Analysis, Reporting, Data Quality, Master Data and Integration Services would be enabled in the new platform, turning the new instances into a true Data Platform with lots of value.
Jane saw an opportunity to turn the negative impression IT had left on the business to a positive one. Now that the plan is to make the servers highly available, as well as consolidated, she believes there's much more they can do with this new SQL Server platform.
Jane asked Joe to install and enable Analysis Services in order to allow her organization to analyze data and provide self-service business intelligence. Furthermore, Jane asked Joe to enable Reporting Services to deliver both predefined, as well as ad-hoc reporting capabilities. Jane remembered a request from the Customer Service Department regarding the quality of customer data. So, Jane asked Joe to enable Data Quality Services and Master Data Services in order to control the quality of data and enforce data quality rules. Last but not least, Jane asked Joe to enable Integration Services to allow the import and export of data from and to the new servers.
Day 3 - Throwing money at the problem
What's the cost of all this?
Cost of consolidation as well as service enablement will be easily recovered by requiring less space in the data center, fewer resources to maintain and less power to run. Furthermore, the organization would gain insights into their business.
Jane was able to sell the plan to her management. However, she was asked what was the cost of this plan. Jane was not sure whether she was introducing additional costs or was she saving the organization some money. She consulted Joe.
Joe explained that one of the pillars of cost is the new hardware. The new servers would cost money, as they have higher processing power. However, the footprint at the Data Center would be reduced dramatically, as the number of SQL Servers would be reduced from eleven to two, requiring less power consumption and less heat dissipation, not to mention that this would require less manpower to manage.
It would cost the organization manpower and consulting services in order to properly install the new servers and configure high availability and SQL Services. However, the organization would gain additional benefits from the new services that add a lot of value to the business like ad-hoc reporting and analytics.