GIS is a very important part of today’s technology. There’s no doubt that the backend of a GIS application requires the involvement of an advanced RDBMS that cannot only handle the workload but all the complexities that come along with the technology, such as the vast indexing, data load, and security among other features.  Upgrading to new versions and applying patches to an application is part of the trend in technology and preparing for a GIS upgrade is not a trivial task and testing is important.  In this article I will mention a specific issue that I experienced during my last GIS application and database upgrade, even though my IT team included the right individuals to assist during the testing phase and the upgrade was successful on our test environment, we faced unexpected results after upgrading the production environment.

As I mentioned in my introduction, the upgrade on our test GIS application and database was performed without any issues.  We upgraded the GIS application from ArcGIS 9.3.1 to ArcGIS 10.0, that process modified the GIS database and its tables.  After the upgrade the public and internal map sites worked as expected, an increase in performance was noticed by several users and the IT staff.  With confidence, we planned, scheduled and implemented the upgrade on the production environment.  Despite the good news from the upgrade on the test environment, the upgrade on the production environment did not give us the exact same results.  

After GIS application and database upgrade on the production server, I noticed that one of my maintenance jobs did not execute successfully against the GIS database. Database maintenance jobs serve several purposes and one example is to run T-SQL commands to check database data integrity.  Data integrity is the consistency and accessibility of the data in a database, having good data integrity ensures the quality of the data.  As a Database Administrator, I have learned to keep maintenance jobs running on a regular basis to check data integrity on my database servers.  These jobs look for data problems that, if found in a timely matter, could be fixed to prevent data loss.  The T-SQL command that checks for data integrity is the “DBCC CHECKDB”.  On each environment, test and production, I have a job scheduled to run the “DBCC CHECKDB” once a week and to check the data integrity on each of my databases. 

The job running “DBCC CHECKDB” on the production server, after the GIS application and database was upgraded started logging errors and fail attempts as well as generating SQL file dumps on the server’s instance directory. See Figure 1 and Figure 2 for an example of the error log entries and Figure 3 to see an example of the SQL file dump.  You can find the SQL error log by opening Microsoft SQL Server Management Studio then connect to the SQL Server instance and go to SQL Server Agent then find the directory Error Logs under it.  The location for the SQL file dump is on the default location for an SQL Server on C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log.  Each file generated had a naming convention as SQLDump0001.txt and it had references to “DBCC CHECKDB (N’GISDatabase’).”  The SQL file dump also had a line of code for an access violation “Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION”.  

Figure 1.

Figure 2.

Figure 3.

After spending a few hours researching this problem we ran across the following article from Microsoft.

            http://support.microsoft.com/kb/2635827

These article states that the access violation error occurs because there exist in the database a table that contains a spatial data type column and the command “DBCC CHECKDB” is running against the database that contains the table. The stated solution was to apply a cumulative patch depending on the SQL Server version.

Evaluating further our environments, we checked the SQL Server version installed on each server.  Typing the T-SQL “Select @@Version” at the query window gave us the following information.

Test environment:

Figure 4.

Production environment:

Figure 5.

As you can see from Figure 4 and Figure 5, the SQL Server versions on test and production are different. Before applying the patch to production, we created an independent new virtual machine with the same SQL Server version as in our production environment, restored the GIS database on that instance and ran the “DBCC CHECKDB” command against it.  This resulted in the same error messages and a SQL file dump being generated.  Then, we applied to the virtual machine the Cumulative Update package 4 for SQL Server 2008 R2 Service Pack 1 (http://support.microsoft.com/kb/2633146) and ran the “DBCC CHECKDB” command again.  This time everything worked perfectly, no errors or SQL file dumps.  Once we felt confident about the solution to our problem, we applied the Cumulative Update package 4 to our production server to fix the issue. 

Realizing that the test environment had been upgraded to a newer version than the production environment, and “forgetting” to upgrade the production server gave us some feelings of discomfort. Time constraints, daily activities, priorities and new projects interfered with our internal plans however those steps backs should not be keep us from moving forward.  It is imperative to keep your environment consistent.