Tags: SQL Server Central Log Shipping Manager, SQL Sniffer
Our customer needed a way to upgrade their unsupported SQL Server databases to newer versions of SQL Server. The major challenge was that they didn’t have visibility into their system. Moreover, Microsoft upgrade tools can’t handle such outdated database versions as SQL Server 2000.
With our innovative database discovery and monitoring tools, we were able to perform an inventory of their system. We developed an upgrade factory to get them back within Microsoft compliance. Now our customer has deep insight into their system and can take advantage of modern SQL Server capabilities with upgraded databases.
Original problem
Our customer faced the situation when essentially 99% of their system databases were within the unsupported platform. The vast majority of these databases were SQL Server 2000/2005 databases. Besides, the connections between customer’s SQL Server 2000/2005 databases and corresponding applications were unknown. That presented an interesting challenge because a lot of database tools like Profiler operate quite differently with SQL Server 2000. Moreover, the old SQL Server 2000/2005 syntax was hard to identify with Microsoft tools like SQL Server Upgrade Advisor.
The applications in the customers’ system were making dynamic queries, so the customer needed to find out which applications were making those calls. The static analysis tools cannot handle this situation, so we had to find a way to identify these applications, and then remediate them.
Network sniffing and replaying approach
Our approach was aimed at reducing the costs and the time associated with the discovery and testing of these applications. We were looking for an automatic solution to identify the applications, hence decided to use nonintrusive network sniffing tools. We developed SQL Sniffer tool that works at the network switch level to solve this problem. Installed on a server in the customer’s environment, SQL Server records all SQL statements coming from the applications to specific SQL Server instances. These records are then being saved as CSV files on the sniffer server.
Another tool, called the Replayer, sent the queries from the CSV files against an upgraded version of the original database and logged the execution errors. That allowed us to find the location of the applications running the queries and the errors associated with that queries. With that visibility, we can easily fix the applications and upgrade the databases to a newer version of SQL Server.
You can find more information about our SQL Sniffer and Replayer tools in the following blog post: /blog/sql-sniffer-replayer/
SQL Server Central Log Shipping Manager
When upgrading a group of the databases, you can face an extended downtime due to the necessity of backing up the source databases and then restoring them on the destination server. As an alternative, we can apply the log shipping to automate the backup of a database and transaction log files on a primary database server. Then we can automatically send these transaction log backups to secondary databases. During this process, we synchronize the source databases with their destination, and then the original databases go offline, while the databases on the upgraded server become available.
We automated this whole process in a single application called SQL Server Central Log Shipping Manager. As a result, with that tool, the downtime required for the upgrade process took 3 minutes or less.
Conclusions and benefits
To deliver this upgrade solution we began with analyzing the original system using DBMSys tool to determine the servers’ workload over time. Then we went ahead to the upgrade process, where we have used our novel SQL Sniffer and Replayer tools to catch all possible upgrade issues. To maintain high-availability during the upgrade process, we developed SQL Server Central Log Shipping Manager. We ended up using Database Compare Suite to validate that no issues occurred after the upgrade.
This set of tools represents an upgrade factory that provided our customer with the following benefits:
- Highly automated and time effective solution
- Reduced overhead on a production system
- Visibility into long running system with tangled connections
- Minimal downtime
Feel free to contact us to upgrade your system with confidence.
