topic Re: Dev sql geodatabase to replace Prod sql geodatabase in Geodatabase Questions

Dev sql geodatabase to replace Prod sql geodatabase

AndreaB_ — Thu, 30 May 2024 15:34:22 GMT

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.

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

Re: Dev sql geodatabase to replace Prod sql geodatabase

MarceloMarques — Thu, 30 May 2024 15:49:36 GMT

@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

Re: Dev sql geodatabase to replace Prod sql geodatabase

AndreaB_ — Thu, 30 May 2024 15:49:57 GMT

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.

Re: Dev sql geodatabase to replace Prod sql geodatabase

MarceloMarques — Thu, 30 May 2024 17:26:35 GMT

@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.

Re: Dev sql geodatabase to replace Prod sql geodatabase

VinceAngelo — Thu, 30 May 2024 17:54:06 GMT

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

Re: Dev sql geodatabase to replace Prod sql geodatabase

MarceloMarques — Thu, 30 May 2024 17:57:08 GMT

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.

Re: Dev sql geodatabase to replace Prod sql geodatabase

AndreaB_ — Thu, 30 May 2024 18:43:19 GMT

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.

Re: Dev sql geodatabase to replace Prod sql geodatabase

MarceloMarques — Thu, 30 May 2024 19:17:47 GMT

"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. : )

Re: Dev sql geodatabase to replace Prod sql geodatabase

AndreaB_ — Thu, 30 May 2024 21:47:39 GMT

"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.

Re: Dev sql geodatabase to replace Prod sql geodatabase

AndreaB_ — Mon, 03 Jun 2024 20:16:01 GMT

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.

Re: Dev sql geodatabase to replace Prod sql geodatabase

MarceloMarques — Mon, 03 Jun 2024 20:42:56 GMT

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!!!

Re: Dev sql geodatabase to replace Prod sql geodatabase

AndreaB_ — Mon, 03 Jun 2024 21:05:25 GMT

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.

Re: Dev sql geodatabase to replace Prod sql geodatabase

MarceloMarques — Mon, 03 Jun 2024 21:08:18 GMT

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.

Re: Dev sql geodatabase to replace Prod sql geodatabase

AndreaB_ — Wed, 05 Jun 2024 20:31:55 GMT

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!

Re: Dev sql geodatabase to replace Prod sql geodatabase

MarceloMarques — Wed, 05 Jun 2024 20:55:33 GMT

@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.

Re: Dev sql geodatabase to replace Prod sql geodatabase

AndreaB_ — Fri, 07 Jun 2024 19:15:39 GMT

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.