Migrating from ArcServer 9.3.1 to ArcServer 10.3.1

[PaulHuffman]

I have ArcServer 9.3.1 running on Windows Server 2003 R2 with Sql Server 2000.  Have a new Windows Server 2012 R2 with ArcServer 10.3.1 on a new virtual server with SQL 2012 running on a different virtual server.  We were hoping that we might be able to make a connection between ArcServer 10.3.1 and Arc Server 9.3.1 and pipe the compatible data to the new server.  The compatibility matrix at Client and geodatabase compatibility—ArcGIS Help | ArcGIS for Desktop seems to say I can connect to the 9.3.1 geodatabases to upgrade.  But we haven't been able to make a gis server connection in Catalog to the old server. 

Even though we could make a connection with SQL 2012 Management Studio to our SQL 2000 database, and I can establish an OLE DB connection in Catalog to old geodatabases,   the ESRI database group has us restoring from the SQL 2000 backups.  However, SQL 2012 couldn't restore from the old backups. The database group told us to restore to SQL  Server 2005, 2008 or 2008 R2 first, change the compatibility, then back it up from there, then restore the new backup on SQL Server 2012.  We found a license for SQL 2008, but found we couldn't install it on Windows Server 2012 R2.  Now we are installing on an older server to see if this will work.  Did I wait too long to upgrade? 

The database group told us that we couldn't use the Management Studio connection, or the OLE DB connection to import files because that would only give us access to the DB tables, not the feature classes and image sets.

But isn't the restores from SQL backups just going to give us the same tables?

[AsrujitSengupta]

To be honest, SQL Server 2000 to SQL Server 2012 is quite a big jump. There was 2005, 2008, 2008 R2 in between. So, yes the wait for upgrade was quite long.

Even though you can connect to a SQL Server 2000 instance from SQL Server 2012 Management Studio, you cannot restore it on the SQL Server 2012 instance directly.

OLE-DB connections are read-only and cannot be used to load data or upgrade the sde geodatabase to a higher version, which you ultimately need. To do the upgrade, you will need a Direct Connection.

[George_Thompson]

Here is a blog outlining the instructions for upgrading the database from SQL Server 2000 to SQL Server 2012.

Migration SQL Server 2000 to SQL Server 2012 - Microsoft SQL Server - Site Home - TechNet Blogs

Once the database is at SQL Server 2012, you could then make a connection in ArcCatalog and then upgrade the geodatabase to 10.3.1.

-George

[PaulHuffman]

Well, I didn't find a suitable place to install SQL 2008 yet.  Now my DBA is going off to float the Tatshenshini River in AK for three weeks. Looks like this is being postponed until August.  I hope that there is not another release between now and then.

[AsrujitSengupta]

How Big are the databases? If they are below 10GB, download a SQL Server 2008 R2 Express edition (for free). This should work on the new server and shouldn't take much time.

[PaulHuffman]

The troublesome geodatabase, the one that didn't work when I did the two step backup/restore,  created a 26 gb xml file.  Now that that import xml workspace document option has been shown not to work for 9.3 to 10.3.1,  I'll have to go back and repeat the two step process or track down where the DBAs inadvertently changed the database name. 

[AsrujitSengupta]

You can check the Table_registry table in the databases. It will show you the Original database name.

[PaulHuffman]

Step 2 on that blog has no details at all, just says Step 2: Make a second migration from SQL Server 2008 to 2012.

But I got it to work.  Took a 2008 backup, restored to 2012.  Now I just need to figure out how update the geodatabase and set up the users.

Updating the geodatabase I got a fail:

Bad login user [42000:[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot execute as the database principal because the principal "sde" does not exist, this type of principal cannot be impersonated, or you do not have permission.]

Bad login user

Unpaused the database.

Failed to execute (UpgradeGDB).

Looks like I need to be connected to the database as user sde,  which was the admin user on the old server and using SQL authenification.  Trying now to give my SQL account owner privileges or make the connection as user sde. 

[AsrujitSengupta]

I guess that is because, the Step-1 was to take a backup at SQL 2000 and restore on SQL 2008, and then Step-2  was to take a backup at SQL 2008 of that same database and restore on SQL 2012.

[PaulHuffman]

This is a helpful article on getting the databases and user accounts all going again after a SQL Server restore: http://support.esri.com/ja/knowledgebase/techarticles/detail/29672.  Got this tip from Asrujit SenGupta at ESRI Tech Support.