Dev sql geodatabase to replace Prod sql geodatabase

[AndreaB_]

Hello,

I have created a new sql geodatabase in my dev environment that has the schema, users, permissions etc set up according to best practices. I now need to replace my prod sql geodatabase with this dev sql geodatabase. I need to get the dev geodatabase to prod and get the data from prod into the dev. I have a rough outline of the steps I think I need to take.

1) Can you review the steps please and give any advice and feedback? I'll put steps below.

2) I'm using Traditional Versioning. When I copy/paste the data into the new Prod from old Prod - do I have to worry about data loss from data in the versioned tables? We won't be able to get to an end state count = 1 in the compress_log.

Steps:

Create a backup of Prod sql db DBNAME

Stop editing in Prod sql db DBNAME, stop ArcGIS Server map and feature services, disconnect users, run through rec/post/delete versions/compress.

Rename Prod sql db DBNAME to DBNAME_ProdOld

backup a copy of dev sql db to Prod, restore it to the DBNAME. Recreate logins, remap data users to logins

Delete all data and tables from DBNAME using ArcGIS Pro

one at a time, copy/paste all feature classes and tables into new Prod DBNAME from DBNAME_ProdOld – using the data owner connection.

Create the relationship classes.

Register the correct feature classes as Versioned

Add Editor Tracking and fields to versioned feature classes

Analyze, rebuild indexes, compress, analyze, rebuild indexes.

Create the 'Working' version

Create geodatabase connection files in ArcGIS Pro as sde user, data owner user, editor, and viewer users to test the geodatabase.

Restart all ArcGIS Server map and feature services

Thank you in advance,

Andrea

[AndreaB_]

Hi @MarceloMarques ,

I'm working through the steps and for #7 "rename DBNAME to DBNAME_OLD" in Production - this could be an issue because the Prod geodatabase is a very old Esri arcsde version 10 repository. So once I rename it, it will break in GIS. Right? And then I won't be able to connect to it in Pro to get the data out using the Append tool. 

[MarceloMarques]

yes, it will break the geodatabase if you rename the database because in that geodatabase the arcsde repository is version 10, and if you rename the database, you will not be able to connect to it. The ability to rename the SQL Server database was introduced in Pro 3.0, this means the arcsde repository must be pro 3.0 or higher for you to be able to rename the SQL Server database.

Tip, create the new production geodatabase with a different name, e.g. DBNAME_NEW, that one will be arcsde repo pro 3.x then you will be able to rename the SQL Server database later, then reload the data, when done take a good backup of the old production DBNAME, drop the DBNAME database in production and then rename the new production DBNAME_NEW to DBNAME ( old production name).
!!! Ecco la soluzione!!!

[AndreaB_ · Accepted solution]

Yup, I was afraid of that. So do you think this workflow would work in Prod?:

restore backup of the DEV DBNAME (empty geodatabase version 11.1) into the PROD server naming it DBNAME_NEW,

Import xml Workspace Document schema-only into DBNAME_NEW from previous export of xml.

load the data into DBNAME_NEW from Prod DBNAME (version 10) using the Append tool 

rename the old Prod database to DBNAME_OLD (version 10, breaking it)

rename the new database to DBNAME (this is the version 11.1)? I'm not as familiar with SQL Server - Is this a rename using SQL Server management studio?

Thanks.

[MarceloMarques · Accepted solution]

Looks good, yes you can rename the SQL Server database using SQL Server Management Studio, right click the database and choose "rename" in the drop down menu.

[VinceAngelo]

IMHO, copy/paste of each feature class is the road to ruin, because all versions need to be deleted and the
entire instance compressed first, or all data in An/Dn tables will be lost.

If you move the data in the database using database tools, you move the versioning as well (the backup needs to span the geodatabase tables -- sde.sde/gdb_* and all the schemas which own tables managed in the geodatabase), and you don't need to have a versioning fire sale (Crazy Vince says all data must go!).

The single biggest issue here is that you can't change the name of the database after restore (that messes up the registry).

If the table schemas have changed in dev, you need a much more detailed migration plan.

- V

[MarceloMarques]

if the geodatabase repository is upgraded with Pro 3.x or later then the SQL Server database can be renamed and that does not break the arcsde repository anymore, and the SQL Server geodatabase continues to work fine. This is a new feature enhancement that was introduced that many customers have requested.