MICROSOFT® SQL Server Database Health Check and Archival Process Creation

ORGANIZATION

This client is a credit card processing company that handles business-to-business transactions and prides itself in having processed transactions completed in under three seconds. Their business model is based on the idea that time is money, and that the more time a company’s payment system takes to execute, the lower the profit margin for that company. For companies that need to process thousands of transactions a day, this client promises a quick and reliable method of getting paid.

CHALLENGE

As a client’s customer base grows, so do that organization’s database needs. Over time, this particular customer has seen a significant increase in the time it takes to process each credit card transaction from under three seconds up to ten seconds. The client contacted XTIVIA to perform a health check to pinpoint the bottleneck. In addition, the client requested XTIVIA’s expertise in creating a custom archival process to reduce the amount of records in active tables using the T-SQL tool. As the client deals in online financial transactions, one of the key challenges was providing a solution in a timely manner, as well as scheduling maintenance outages to implement changes.

SOLUTION

XTIVIA completed a health check of the client’s SQL Server environment and identified several configuration settings that needed to be configured appropriately. Indexes were also heavily fragmented due to a growth in activity as well as a lack of proper maintenance. In addition, XTIVIA developed a custom archiving process which archives records to a reporting database, then eventually to an archive database after 90 days.

RESULTS

XTIVIA was able to implement all changes during scheduled outages over time. Since the databases needed to be online as close to 100 percent as possible, work was planned out, and then performed quickly and efficiently when the outages could be arranged. Thanks to augmenting the SQL Server configuration settings and best-practice index maintenance, the client is now seeing sub-second processing speeds for their transactions, which is a vast increase in processing capability for this client. The primary table now only holds 90 days of active records, and refreshes daily, allowing the database to maintain the sub-second processing speeds. In addition, the client now has XTIVIA on their side to help tune their database when needed, allowing the client to focus on their growth while XTIVIA helps them handle their demands.

CHALLENGE

A large provider of online training and employee certifications needed to migrate 10 Microsoft® SQL Servers from one hosting center to another. This was done to consolidate their hosted systems and save money on hosting services. The servers were a mix of old SQL 2000 instances, SQL 2005 and SQL 2008 systems. The SQL Servers contained databases, as well as supporting objects, ranging from DTS packages to stored procedures, to Integration Services packages, scripts and even Microsoft Analysis Services databases, and a Reporting Services installation. Databases and supporting objects all had to be moved during a one day window.

KEY COMPONENTS

SOFTWARE Microsoft® SQL Server SERVICES Database Practice: Custom Coding SQL Server Database Health Check DOWNLOAD THE CASE STUDY PDF