topic Re: SDE - Relocating Database to New Instance in Geodatabase Questions

SDE - Relocating Database to New Instance

JD1016 — Tue, 15 Nov 2022 17:11:10 GMT

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

Re: SDE - Relocating Database to New Instance

MarceloMarques — Tue, 15 Nov 2022 17:27:04 GMT

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

Re: SDE - Relocating Database to New Instance

JD1016 — Tue, 15 Nov 2022 17:21:09 GMT

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

Re: SDE - Relocating Database to New Instance

Asrujit_SenGupta — Tue, 15 Nov 2022 17:23:06 GMT

How To: Re-synch SQL Server logins or users after restoring a database from backup

You'll need to create the required logins at the Instance level, before you Re-synch..

Re: SDE - Relocating Database to New Instance

MarceloMarques — Tue, 15 Nov 2022 17:26:08 GMT

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.

Re: SDE - Relocating Database to New Instance

JD1016 — Tue, 15 Nov 2022 17:34:10 GMT

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.

Re: SDE - Relocating Database to New Instance

MarceloMarques — Tue, 15 Nov 2022 17:49:51 GMT

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

Re: SDE - Relocating Database to New Instance

JD1016 — Tue, 15 Nov 2022 17:57:40 GMT

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.

Re: SDE - Relocating Database to New Instance

JD1016 — Tue, 15 Nov 2022 18:16:18 GMT

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.

Re: SDE - Relocating Database to New Instance

MarceloMarques — Tue, 15 Nov 2022 18:28:27 GMT

see example below

Re: SDE - Relocating Database to New Instance

JD1016 — Tue, 15 Nov 2022 19:04:27 GMT

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.

Re: SDE - Relocating Database to New Instance

MarceloMarques — Tue, 15 Nov 2022 19:19:21 GMT

see example below, change the text <db_name> and <user_name> and <user_login>

USE master
GO
--<db_name>
CREATE LOGIN <login_name> WITH PASSWORD = '****', DEFAULT_DATABASE=[<db_name>],DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
--sde
CREATE LOGIN sde WITH PASSWORD = '****', DEFAULT_DATABASE=[<db_name>],DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE <db_name>
GO
ALTER USER sde with LOGIN=sde
go
ALTER USER <user_name> with LOGIN=<user_login>
go

Note: if you do not have a sde user that is the owner to the geodatabase repository and instead the sde repository tables are owned by dbo then skip the part that creates the sde login and remaps the sde user to the sde login.

Then, do not forget to change the SQL Server Compatibility Version to "SQL Server 2019".
View or change the compatibility level of a database - SQL Server | Microsoft Learn

I hope this clarifies now.

Re: SDE - Relocating Database to New Instance

JD1016 — Wed, 16 Nov 2022 12:10:17 GMT

I was about to send you some questions regarding the query but this came up.

Not quite sure what is going on, but today I am showing "SQL user without login" on each of my Users. Please see screen capture.

If you recall yesterday I had checked each of my Users and they had identical logins to the User Name except for one.

Re: SDE - Relocating Database to New Instance

MarceloMarques — Wed, 16 Nov 2022 16:27:38 GMT

"SQL user without login" means that your SQL Server database is configured with contained users, read more in the link below, if you have contained users then is not necessary to configure SQL Server Logins.
Contained Databases - SQL Server | Microsoft Learn

Note: if you are still not able to connect to the database from a remote client then read the link below.
How to Enable Remote Connections on SQL Server - TechNet Articles - United States (English) - TechNet Wiki (microsoft.com)

Remarks: seems that your enterprise geodatabase in SQL Server was not configured with best practices and has a mix of different user authentication methods (login and contained), and that you will need to determine why your database was setup like that, normally we use one or the other, the contained users make it easy to move the database because is not necessary to remap the users to logins.

I hope this helps.

Re: SDE - Relocating Database to New Instance

JD1016 — Wed, 16 Nov 2022 17:02:43 GMT

I'm slowly finding out the history of this database and it is not encouraging and would explain many of the issue I am having with rationalizing the structure.

On a separate note but still connected. I was experimenting on our new server and came across the following. Please see attached. Instead of using database authentication I used windows authentication and my restored databases came up as selections (sturgis01 and Raster). They were both able to be added as database connections using Catalog. Everything is there and available referencing the new server. Do you see any inherent red flags that I should be concerned with if I used that authentication?

Thanks.

Re: SDE - Relocating Database to New Instance

MarceloMarques — Wed, 16 Nov 2022 17:55:09 GMT

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.

Re: SDE - Relocating Database to New Instance

JD1016 — Wed, 16 Nov 2022 18:10:16 GMT

Understood. I'm going to create a new database from scratch. With all the issues with this one not following best practices in its creation is not an ideal situation going forward. I'll retrieve the data and import them into a new structure.

That being said...do you have any recommended links that you can pass along that will guide me through the creation process using best practices?

Thanks.

Re: SDE - Relocating Database to New Instance

MarceloMarques — Wed, 16 Nov 2022 18:47:45 GMT

Follow the Enterprise Geodatabase for SQL Server best practices and guides books that I wrote and maintain.

Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

1. read the "Production Mapping" guidebook for SQL Server, there is the Pro version and the ArcMap version
2. download the database template script for SQL Server, you can customize it to meet you needs.

Notes: the guidebooks and database templates are for advanced SQL Server Administrators & Geodatabase Administrators, if you do not have the proper training those will be very difficult to understand. I do not provide any support for the database template scripts and guidebooks, those are free best practices, and you got what is there, no free consulting of course, to get more help Esri Professional Services can be reached to provide assistance through a billable engagement.

Re: SDE - Relocating Database to New Instance

JD1016 — Wed, 16 Nov 2022 18:48:58 GMT

Thank you for all your time and effort, Marcelo! Truly appreciated.

Jeff