Tags: SQL Sniffer
Do you still have SQL Server 2000 database servers running in your organization? Do you have SQL Server 2008 R2 or older database servers running with databases using compatibility level of 80 for SQL Server 2000 compatibility? Running the SQL Server profiler over an extended period of time looking for deprecated and discontinued syntax may not be an option either. At DB Best, we have developed a solution called SQL Sniffer that works at the network switch level to solve this problem. By using the port mirroring feature at the network switch, SQL Sniffer looks for SQL statements coming from applications to specific SQL Server instances. SQL Sniffer writes the statements to log files on the sniffer server. We have a separate process called Replayer that submits the SQL statements a newer version of SQL Server against a backup copy of the database to see what errors occur. With the application location and errors in hand, it’s now easier to fix the application so you can move the database to a newer version of SQL Server.
What about the alternative approaches?
With the SQL Server Upgrade Advisor, you can perform a static analysis of database objects. The database objects represent a small portion of the problem when you consider that applications are likely to send dynamic SQL statements to the database.
The natural alternative is to create a server side or SQL Profiler trace to look for the dynamic SQL statements and then use the Upgrade Advisor to analyze the trace files. The problem with using tracing is the high overhead on a production system on the capture side along with enough specific information to figure out where the statements are coming from.
Enter SQL Sniffer. By cloning the SQL traffic at the network switch level, logging the traffic and then playing it back on a newer version of SQL Server, you can address the following problems:
- No strain on your current production server
- You know where in your organization the SQL statements are coming from
- You know exactly what the problem SQL statements using the Replayer
- Automation of playing back the log files dramatically reduces the overhead of analyzing log files
How does SQL Sniffer work?
SQL Sniffer uses the WinPcap 4.1.3 network protocol analyzer to look for the TDS protocol packets going through the network. By using the port mirroring feature on your network switch, the TDS packets are forwarded to the SQL Sniffer system. The SQL Sniffer system (listed below as the Testing Client) is typically a virtual machine on your network and is used to decode the captured packets and format them to CSV files with the recorded SQL statements along with the user name and application IP address.
SQL Sniffer uses the name of the SQL Server host file along with the port number and server credentials to for tracking packets. In addition, you can specify specific client IP addresses to filter on during testing. Once you start the SQL Sniffer service, it creates report files that can be used for analysis and playback against a newer version of SQL Server.
How does the Replayer work?
The first step is to backup the SQL Server 2000 database and restore to a SQL Server 2008 R2 server. We then run a script that truncates all of the data. That database is then backed up and restored to a SQL Server 2014/2016 server that Replayer uses for the playback. Next, we have an automated process that plays back the queries captured by SQL Sniffer against the SQL Server 2014/2016 server using the system and captures any errors. Replayer is also optimized to remove duplicate SQL commands or SQL queries that use different parameters. We can then correlate the errors with the original clients to identify which applications need to be fixed.
SQL Sniffer also works with Oracle and Sybase ASE
Because DB Best also helps customers with database migrations, SQL Sniffer also supports network protocols for Oracle Database 10g and newer along with Sybase ASE versions 15.7 and 16. We can then use the captured log files and process them using the SQL Server Migration Assistant (SSMA) as part of a migration project.
SQL Sniffer aids in capturing and reduces testing time
As you can see, SQL Sniffer provides an effective way to capture dynamic SQL statements application by avoiding SQL tracing which can impact performance on the current production server. The use of Replayer reduces the testing time by automating the playback and capturing any errors that occur. You don’t necessarily have to use SQL Sniffer for just SQL Server 2000 upgrades either. You can use the same approach for SQL Server 2008 and SQL Server 2008 R2 databases to SQL Server 2014/2016. You can simply use SQL Sniffer over time to monitor the statements over time and play them back against the newer version of SQL Server. If you don’t see any errors, you can likely upgrade without any issues. Of course, there is a lot more to upgrading the database and going into production, but SQL Sniffer and Replayer helps resuce your overall costs for upgrade projects.
Where to go for more information?
If you want to learn more about how SQL Sniffer and Replayer can help you for your database upgrade projects, please contact us today.
