Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcSDE.

[El_BoukfaouiReda]

Dear,


I am working in a highly configured three tier architecture where I need to connect to a database server.
I have access to the virtual GIS server and my database server.
The database that I want to connect to is an SQL Server 2008 R2 created and stored in the database server.
So, in my GIS Server, after verifying and performing all the necessary prerequisites actions :
- Installing SQL Server Native Client 2012 in the GIS Server.
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//002q000000m5000000

I have gone through the ArcSDE installation and I checked only the option to "enable geodatabase storage on SQL Server Express"
as soon as MS SQL Server 2008 R2 as well as the database are stored within the Database Server, so as per my understanding, I do not need to install SQL Server Express 2008 on my GIS Server.

Now the problem is as follows:
When I want to enter the Database Server SQL Server Instance Name, I do not have the choice to change it from the dropdown menu(screen.jpg).
What could be the solution?


Technical specifications:
O.S: Windows Server 2012 R2(GIS Server & Database Server).
ArcGIS Desktop 10.1 (GIS Server)
ArcGIS for Server 10.1 (GIS Server)
SQL Server 2008 R2 (Database Server).


Please help asap !
Thank you!

[VinceAngelo]

No, Marco, Create Enterprise Geodatabase will still work on an existing database with
an 'sde' user already created (it just gives messages about the database and user
already existing).

- V

[El_BoukfaouiReda]

Yes, basically, that is the procedure, but also note that if you already have an existing database with users etc., and you wish to use this existing database by converting it to a geodatabase, than you will need the Enable Enterprise Geodatabase tool rather than the Create Enterprise Geodatabase tool. Of course, if you want to start from scratch with a new database, than Create Enterprise Geodatabase is the right tool.

I used Create Enterprise Geodatabase tool and it worked fine !
I created my Database connection with the newly created database.

Now, when I decided to export the XML Workspace document (schema+data) of my .gdb to the .sde SQL Server Enterprise Geodatabase, it says that :
''Import XML data failed.
Database user name and current use schema do not match.''

I tried also with CopyFeatures tool as well as Exporting the feature classes from their respective feature datasets to the .sde newly created datasets but it is not working...

Have I missed something regarding the database permissions? or do I still need to add a super user?
I am confused, please help !



Regards.

[WilliamCraft]

I used Create Enterprise Geodatabase tool and it worked fine !
I created my Database connection with the newly created database.

Now, when I decided to export the XML Workspace document (schema+data) of my .gdb to the .sde SQL Server Enterprise Geodatabase, it says that :
''Import XML data failed.
Database user name and current use schema do not match.''

I tried also with CopyFeatures tool as well as Exporting the feature classes from their respective feature datasets to the .sde newly created datasets but it is not working...

Have I missed something regarding the database permissions? or do I still need to add a super user?
I am confused, please help !



Regards.

You may be better off using a manual copy/paste of your feature datasets, feature classes, and tables in ArcCatalog if you're trying to migrate data from a file geodatabase to an enterprise geodatabase as a one-time workflow.

[VinceAngelo]

"User name and schema do not match" is pretty explicit.  What user name and
schema are you using?  They must be the same to create data.

- V

[AsrujitSengupta]

Change the Default_schema of the user similar to the username, in user properties from SQL Server Management Studio.

Open SQL Server Management Studio-->Connect to Instance-->Security--> Logins--> R-Click on the Username(being used to load data into sde)--> Properties--> User Mapping--> Changed the "Default Schema" similar to User.

Adding database-authenticated logins to a SQL Server database:
http://resources.arcgis.com/en/help/main/10.2/index.html#//002q0000002m000000

Check Point #14 in the above link..

[El_BoukfaouiReda]

You may be better off using a manual copy/paste of your feature datasets, feature classes, and tables in ArcCatalog if you're trying to migrate data from a file geodatabase to an enterprise geodatabase as a one-time workflow.

I am not only dealing with feature datasets and classes, but also with proper relationships, domains and subtypes, copy/paste would not be very much relevant to my case.

"User name and schema do not match" is pretty explicit. What user name and
schema are you using? They must be the same to create data.

Exactly, Database user name (add Database Connection) was not matching with the Database Administrator name (Create Enterprise Geodatabase).

[WilliamCraft]

"I am not only dealing with feature datasets and classes, but also with proper relationships, domains and subtypes, copy/paste would not be very much relevant to my case."

I assume by proper relationships you mean relationship classes.  The copy paste method in ArcCatalog respects relationship classes, domains, and subtypes amongst many other properties in your geodatabase and it's objects.  In other words, a copy paste brings everything over that you need when picking feature datasets, feature classes, and tables.  You would need to perform a mass selection using shift + click when performing the copy.   I think it IS a relevant option for you.