SDE - Relocating Database to New Instance

JD1016 · ‎11-15-2022

Hello,

I recently created a post entitled "SDE - Enterprise Geodatabase Location to New Server" and Marcelo Marques was extremely gracious and provided some key documentation to aid me in my quest. However, I have hit another snag in my journey.

One of the links provided talked about creating a backup of the database and restoring it to a new SQL server instance. I was successful in creating a backup of my database on my old server (gisserver2) using SSMS (14). This backup file was copied over to our new server (gisserver03). I then used SSMS (18) to restore this database on the new server. I then clicked "Add Database Connection" in ArcCatalog 10.8.2 and completed the Database Connection dialog box as seen in the attached. I at first used gisserver03 as the Instance, but of course, the authentication is still running through my old server and it worked fine with those old server credentials.

Can the Instance be modified somehow to our new server (gisserver03) AND still keep the same credentials for User name and password that were applied to the old server (gisserver2)? This server will soon be retired so connectivity will eventually be broken.

On our new server we are using SQL Server Management Studio v18.12.1, ArcGIS Server 10.9.1, ArcGIS Data Store 10.9.1, ArcGIS Web Adaptor 10.9.1, and employ ArcGIS Server Manager. I am not using ArcGIS Pro for this manipulation.

Thank you for your time!

Jeff

MarceloMarques · ‎11-16-2022

you can connect with windows authentication probably because your domain login is sysadmin in the SQL Server instance, the recommendation, you shall not add other logins as sysadmin, why? security of course, you could add other domain logins to the SQL Server Logins and then add those same logins as users in the database and grant select, insert, update, delete on the data owner tables to those users, this will improve security, see the point? security is very important ortherwise your database will be exposed to unauthorized access and SQL injections if you are deploying ArcGIS Server Services. I hope this clarifies.

| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Certified Professional | "In 1992, I embarked on my journey with Esri Technology, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release. Over the past 32 years, my passion for GIS has only grown stronger." | “ I do not fear computers. I fear the lack of them." Isaac Isimov |

View solution in original post

MarceloMarques · ‎11-15-2022

You need to open SSMS then open a new Query Window and use the example below to create the logins on your new SQL Server Instance then remap the users inside your database to match the logins, only then you will be able to create a geodatabase connection file in ArcCatalog / Pro that connects fine. I hope this clarifies.

Example:

USE master
GO
--topo
CREATE LOGIN topo WITH PASSWORD = '****', DEFAULT_DATABASE=[topo1],DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
--sde_topo1
CREATE LOGIN sde_topo1 WITH PASSWORD = '****', DEFAULT_DATABASE=[topo1],DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE topo1
GO
ALTER USER sde with LOGIN=sde_topo1
go
ALTER USER topo with LOGIN=topo
go

| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Certified Professional | "In 1992, I embarked on my journey with Esri Technology, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release. Over the past 32 years, my passion for GIS has only grown stronger." | “ I do not fear computers. I fear the lack of them." Isaac Isimov |

JD1016 · ‎11-15-2022

Hello Marcelo!

Also...one more thing...unfortunately I am not a programmer and I wouldn't even know where to begin.

Sorry for being such a novice.

Jeff

MarceloMarques · ‎11-15-2022

You need to open SSMS then open a new Query Window and use the example to create the logins on your new SQL Server Instance then remap the users inside your database to match the logins, only then you will be able to create a geodatabase connection file in ArcCatalog / Pro that connects fine. I hope this clarifies.

| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Certified Professional | "In 1992, I embarked on my journey with Esri Technology, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release. Over the past 32 years, my passion for GIS has only grown stronger." | “ I do not fear computers. I fear the lack of them." Isaac Isimov |

JD1016 · ‎11-15-2022

Marcelo,

I've opened a new query window in SSMS as shown in the attached screen capture. The sturgis01 database is what I would like to work on first.

Can you tell me what specific locations in your query that I need to modify to fit my scenario?

Thanks.

MarceloMarques · ‎11-15-2022

1. you need to identify all the users and logins in your old database

SSMS open the old database, then open the properties of each user, and record the user name and the login name that the user is mapped.

2. then open SSMS connect to the new SQL Server Instance, open a Query Windows and use the example I gave you above to modify the script, for more information read the esri support article.
How To: Re-synch SQL Server logins or users after restoring a database from backup

| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Certified Professional | "In 1992, I embarked on my journey with Esri Technology, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release. Over the past 32 years, my passion for GIS has only grown stronger." | “ I do not fear computers. I fear the lack of them." Isaac Isimov |

JD1016 · ‎11-15-2022

Okay. Let me see if I can work my way through that process. I wanted to make sure I reached out to thank you for helping me on this before getting started.

Thanks, Marcelo.

JD1016 · ‎11-15-2022

Okay. For step 1...some clarification...since I did not create this database...

Hopefully this is the correct location I should be looking...I believe these are the "user names" but I'm not sure where to locate the "login names"?

Please see screenshot...

Thanks.

MarceloMarques · ‎11-15-2022

see example below

| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Certified Professional | "In 1992, I embarked on my journey with Esri Technology, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release. Over the past 32 years, my passion for GIS has only grown stronger." | “ I do not fear computers. I fear the lack of them." Isaac Isimov |

JD1016 · ‎11-15-2022

Okay. Step 1 is complete. For the most part, except for one instance, the user name matches the login name.

As for Step 2, I don't even know where to begin. The instructional link that you and Asrujit so graciously provided is way over my head. Perhaps if I had some "real" examples I could correlate to my own findings. For instance...my first has a user name of "BASE" with a login name of "BASE"...where does that go in the query? Also if I could understand where in the process we are changing the database from referencing the older server to our new one that might help as well...very lost...apologies for not knowing more on this subject.