Uprading ArcSDE From SQL Server 2000 to 2005

Here is a guest post from a developer friend of mine, Darina Tchountcheva who works at the city of Clearwater, Florida. I first met Darina when ROK was contracted by the city to assist them in some ArcSDE and ArcIMS migrations. It quickly became clear that I was not needed, and that Darina was going to teach me a thing or two. Darina is an amazing talent, who has also had her ArcIMS site recently showcased by ESRI...check it out here.

Anyway, without further yapping from me, here is Darina's excellent instructions on migrating ArcSDE from SQL Server 2000 to 2005.

Upgrading SDE from SQL Server 2000 to 2005.

I was looking for a step-by-step upgrade guide, and I decided to write down my upgrades steps once I go through the process. And, of course, my scenario might not be the same as yours, but if it is, I hope, it will help you get prepared and move on to SQL Server 2005. (I am kind of late, right? SQL Server 2008 is coming! Microsoft, please, slow down!)

I am not an expert SQL Server administrator, so forgive me if I haven't done everything perfect.

My configuration and scenario:

- ArcSDE 9.2 SP3, multi-database model - upgrade must happen on the same server - Intel, 32 bit, Windows 2003 SP2 - server must be rebuild and RAID configured again, because of a system drive problem - clean install of SQL Server 2005 SP2 - the upgrade must work or I am busted. :-)

Steps:

1. Get Prepared!!! - here are some articles that I received from ESRI support, which helped me get started:

http://www.sqlservercentral.com/articles/Administration/2987/

http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx#EAH

2. Decide what your upgrade path will be: in-place or side-by-side

3. Take care of transfering your users. This step must be done while the old (SQL Server 2000 SDE) is still running!

Why do you need this step? If you perform a side-by-side upgrade, a new master database 2005 will be created for your SQL Server 2005 version. There will be no logins in this database that match the users that you currently have in your spatial database(s). The script provided in the article below will create script, which you will run on your SQL Server 2005 master database and will recreate all of the logins (with their passwords) you currently have in your SQL Server 2000. Run the script and save the output as .sql or .txt file on a save location.

http://support.microsoft.com/?id=246133

And read this article to get a better understanding of how users and schemas in SQL Server 2005 affect SDE 9.2:

http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?topicname=users_and_schemas_on_sql_server_2005

4. Compress SDE database. Backup all GIS databases (if you use multi-database model you have more than one). Backup master and msdb databases.

Important: From here the steps are specific for side-by-side upgrade.

5. Decide how you will move the databases: attach, or restore. I used attaching.

If you are going to use attaching: stop the SDE service, stop SQL Server service, and copy the data+log files of all databases (including master and msdb) to a safe location. If you are restoring, you will be using your backups. Here is a link that will help you with this:

http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=About_exporting_and_importing_ArcSDE_geodatabases&anchor=movess

6. Make sure you have some documentation and a plan how to move your DTS packages.

Luckily, most of my complicated stuff is written in PL/SQL and resides on the Oracle databases end. The few I have in SQL Server I rewrote in SSIS before the upgrade.

7. Run the SQL server Uprade Advisor, and see if there are any major problems that might occur. I personally had only DTS packages warnings and the weird Metadata packages error, which I ignored.

http://msdn2.microsoft.com/en-us/library/ms144256.aspx

8. Start upgrading (this is side-by-side upgrade):

- install SQL Server 2005

- install SQl Server 2005 SP2

- Open SQL Server Management Studio and make sure you can connect.

- Open New Query window and load the script you saved in step 3. Check to see of any of the users have a default database different than master. Change it to master, because the other databases are still not existing. Later on you can use the same Alter statements to assign the correct default database to your users. Run the script. Check to see if all the logins are created successfully. Try connecting using some of the logins as a test.

- attach the databases (do not attach master and msdb). Or if you decided to do a restore, restore your databases. Do not change the names of the databases. Open them and see if you can see tables, views, stored procedures, users. I used attach, and when I checked the users, I found out that SQL Server has created a schema for each one of the users in every database, and made the user the owner of the schema. I don't think I need a schema for every user (only for the ones creating datasets, features classes, relationships...), but they are already there, so I kept them.

Here is a link that might give you some insights on the topic: http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=The_ArcSDE_administrative_account - change the compatibility level of your databases - I personally didn't need to change to Single_User mode:

http://blog.sqlauthority.com/2007/05/29/sql-server-2005-change-database-compatible-level-backward-compatibility/

- install ArcSDE 9.2

- run Post Installation - use the provided Installation guide and follow your scenario: dbo or sde schema.

- apply the ArcSDE 9.2 SPX (X - the current SP of all ArcSDE clients).

- open ArcCatalog and connect as administrator, and as a regular user to test the SDE. Hope, you are happy right now and there are no problems that you need to resolve.

- at this point I configured sQL Server Mail and some of my maintenance plans to run during the night - backup, and update statistics.

- everything else is up to you.