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

[MarceloMarques]

@AndreaB_ - yes, the steps you listed sounds good, I see what you are trying to do, copy the new Geodatabase that has the new structure into the Production server, delete the data and then reload the data from the old Prod database into the new one.

To move the database using a database backup.

How to Move the SQL Server Enterprise Geodatabase with a database backup

For more white papers see the Frequent Asked Question under my main blog page below.

Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

[AndreaB_]

Hi @MarceloMarques,

Thanks! Yes, I definitely read through that already and will be following that. Some of my steps I wrote were inspired from your document. 

But my questions still remain as they aren't addressed in your documents, from what I can tell. Because I'm doing more than just moving it. And I also have versioning. 

Thank you.

[MarceloMarques · Accepted solution]

@AndreaB_ 

DEVELOPMENT: 
1. backup sql server database in the dev server DBNAME, this is the new geodatabase with the new structure.

2. restore a backup of DBNAME in the dev server with a new name DBNAME_2

if the arcsde repository is pro 3.x or later then this will not break the geodatabase.

3.create new geodatabase connection files for DEV DBNAME_2

4. drop the data from the data owner on DEV DBNAME - featureclasses, tables, featuredatasets

5. delete all geodatabase domains on DEV DBNAME

6. delete all geodatabase versions  on DEV DBNAME - traditional version or branch version

7. execute a sde compress on DEV DBNAME - if using traditional version

8. gather new statistics, rebuild indexes for DEV DBNAME

9. create a sqlserver database backup of DEV DBNAME

PRODUCTION:

1. create a sqlserver database backup for DBNAME

2. reconcile and post all version child of sde.default - traditional version

3. delete all versions child of sde.default

4. execute sde compress 3 x

5. gather new statistics, rebuild indexes, gather new statistics again

6. create a sqlserver database backup for DBNAME

7. rename DBNAME to DBNAME_OLD

must disconnect all users and stop all arcgis server services

8. create new geodatabase connection files

9. restore backup of the DEV DBNAME (empty geodatabase) into the PROD server

10. create new geodatabase connection files for the NEW PROD DBNAME

11. Pro export xml document schema-only from PROD DBNAME_OLD

12. Pro import xml document schema-only into NEW PROD DBNAME

13. Pro Append Data GP Tool to load data from PROD DBNAME_OLD into NEW PROD DBNAME

note: this is the best method to load data for very large datasets than using copy/paste of featureclass and tables

14. After data load in the NEW PROD DBNAME

a. gather new statistics

b. rebuild indexes

c. rebuild spatial indexes

d. recreate relationship classes

e. recreate topology, geometry network, utility network, etc.

f. enable editor tracking

g. enable geodatabase archiving

h. register as versioned - traditional or branch

i. update statistics

j. update roles permission for editor and viewer user

15. take a sqlserver backup of the NEW PROD DBNAME

16. restart ArcGIS Server services

17. Use ArcGIS Pro 3.3 Update Data Source Tool to update the data source of the ArcGIS Server Services.

If the tool does not work for some reason, then you will need to republish the services.

if the old geodabase has DBO arcsde repository then all featureclasses and tables are DBO owner, and the ArcGIS Server services reference the data as DBO.

Thus, it is necessary to use the Pro 3.3 Update Data Source Tool once the new geodatabase has SDE user repository and a data owner user and editor and viewer users.

Please, read my database connection best practices below to decide which user you shall use to publish or to change the data source of the ArcGIS Services.

• Database Connections Best Practices

Depending on the ArcGIS Server version that you are using the Pro 3.3 Update Data Source Tool might not work, your ArcGIS Server must be 11.2 / 11.3  for this to work. Hence be prepared to have to republish all your services in the worst-case scenario.

I hope this clarifies.

[AndreaB_]

Thank you @MarceloMarques ! This is exactly what I needed and there were a few steps I was missing. To address a few of your items:

Yes, my new DEV DBNAME going to Prod is 3.1 arcsde repository, I used Pro 3.1.5 to create it. So I can rename the geodatabase after moving it, Thank goodness! One of the best enhancements yet.

I won't be able to delete all child versions of sde.default from current Prod DBNAME. I know because I tested  a copy in the dev environment. There are some weird left over replica versions from offline collector app from 2019 and it errors when I try to delete them. Hopefully this won't matter.

The current Prod DBNAME uses SDE user repository, however, SDE user also owns all of the data. I used an Operating System authentication database connection to publish the current feature/map services (signed in as me). The ArcGIS Server service account is a domain user in the AD group assigned the gis_dataeditor role in SQL. Do you think I'll have to republish the feature/map services in this scenario? Our ArcGIS Server is 11.1 so the Pro 3.3 Update Data Source Tool won't work.

Thanks.

[MarceloMarques · Accepted solution]

"I won't be able to delete all child versions of sde.default from current Prod DBNAME. I know because I tested  a copy in the dev environment. There are some weird left over replica versions from offline collector app from 2019 and it errors when I try to delete them. Hopefully this won't matter."

[Marcelo]: unregister the replicas and you will be able to delete the versions that reference the replicas.

"The current Prod DBNAME uses SDE user repository, however, SDE user also owns all of the data. I used an Operating System authentication database connection to publish the current feature/map services (signed in as me). The ArcGIS Server service account is a domain user in the AD group assigned the gis_dataeditor role in SQL. Do you think I'll have to republish the feature/map services in this scenario? Our ArcGIS Server is 11.1 so the Pro 3.3 Update Data Source Tool won't work."

[Marcelo]: do not load data with the sde user, this is a very bad practice, the sde user is the arcsde repository owner and the sde user shall be used only for geodatabase administrative tasks, such as the sde compress. The data needs to be loaded with a data owner user. The ArcGIS Server windows service is running under a Domain User and the Domain User was added to the database and granted the "gis_editor" role, good this is the right thing to do for permissions and security. You can give it a try with the ArcGIS Pro 3.2 /3.3 Update Data Source Tool and see if that changes the data source of your services. The Update Data Source Tool was enhanced at Pro 3.2 to be able to update the data source of services, hence the Pro 3.1 Update Data Source will not work, if you use the Pro 3.2 / 3.3 Update Data Source Tool there is a change it might not work because your ArcGIS Server is 11.1, hence the changes are that you might need to republish the services. If you were using Pro 3.2 / ArcGIS Server 11.2 or Pro 3.3 / ArcGIS Server 11.3 then there is a higher change the Pro 3.2 / 3.3 Update Data Source Tool would work.

I hope this helps.   : )

[AndreaB_]

"unregister the replicas and you will be able to delete the versions that reference the replicas." --- I tried this on the dev copy and it worked perfectly! thanks!

I'll be trying all of these steps in the future. Thanks again.

[AndreaB_]

Hi @MarceloMarques ,

I am looking into the Update Data Sources Tool and I'm not entirely sure that tool will update anything with the Map Services - it says 

"There are limitations to updating data source paths from a catalog view. The following layer types and workspaces are not supported using this method:

• Map service layers"

Have you been able to test this tool?

Thank you!

[MarceloMarques]

@AndreaB_ - yes, the Update Data Source Tool was enhanced at Pro 3.2 to work with Feature Services, etc. If you run into any issues then please open a ticket with Esri Technical Support and let them know, this will help to improve the tool if it is still not working as expected with Map Services, Feature Services, etc.

If the tool does not work at all then the only alternative is to republish the services.

My word of advice before you republish the ArcGIS Server services, make sure the SQL Server Instance is the "default" instance and not a named instance, the default instance allows to connect using only the server hostname, next do not use the hostname in the geodatabase connection files, instead create a DNS Alias that resolves to the production database server, I always set my database servers with a static ip address, hence make the DNS Alias to resolve to the static ip address of the production database server, then create the geodatabase connection files using the DNS Alias. This allows later for more flexibility if you have to move the databases from Server A to Server B in the future, like when we need to upgrade to a new Windows OS and a new version of SQL Server, then the migration is simple, just need to remap the DNS Alias to the static ip address of Server B, and the ArcGIS Server Services will continue to work.

I hope this helps.

[AndreaB_]

Hi @MarceloMarques,

I just wanted to follow up on the Update Data Sources Tool at version 3.3. I had a call with Esri Tech support today and they confirmed that this tool does not work with Feature Services. That would be wonderful if it did. I will plan to republish my feature services by using the "overwrite" option.