SDE - Relocating Database to New Instance

[JD1016]

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]

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

[JD1016]

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]

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.

[JD1016]

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]

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 

[JD1016]

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]

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]

see example below

[JD1016]

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.