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; } } }

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

Database Connection to Old Server.pdf

Find more posts tagged with

Accepted answers

Marcelo Marques

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.

All comments

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.

SSMS Query Window.pdf

Marcelo Marques

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.

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

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

Marcelo Marques

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

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.