nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

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

Find more posts tagged with

Enterprise geodatabase

Geodatabase Administration

Accepted answers

MMBR

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

MMBR

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

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.

MMBR

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.

All comments

MMBR

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

MMBR

@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

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

I hope this clarifies.

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

MMBR

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.

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.

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.