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]

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.

Read more here: A comparison of geodatabase owners in SQL Server—ArcGIS Pro | Documentation

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.

[JD1016]

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.

[MarceloMarques]

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

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.

[JD1016]

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.

[MarceloMarques · Accepted solution]

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.

[JD1016]

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.

[MarceloMarques]

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.

[JD1016]

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

Jeff

[Asrujit_SenGupta]

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