topic Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS in ArcGIS Runtime SDK for Android Questions

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

El_BoukfaouiReda — Mon, 16 Dec 2013 11:59:50 GMT

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!

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

MarcoBoeringa — Mon, 16 Dec 2013 13:53:31 GMT

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 :
...
I have gone through the ArcSDE installation and I checked only the option to "enable geodatabase storage on SQL Server Express"

I don't understand what you are trying to accomplish, either you run an enterprise grade SQL Server 2008 R2, or you run a limited SQL Server Express that only allows 10GB storage max.

You can't use the tools for enabling a geodatabase on SQL Server Express, to enable a geodatabase on SQL Server 2008 R2.

* If trying to create / enable a geodatabase on SQL Server 2008 R2 enterprise edition:

- Run the Create Enterprise Geodatabase or Enable Enterprise Geodatabase tools from ArcToolbox (whichever is relevant for your situation, since it seems you already created a 2008 R2 instance and database, it probably is Enable Enterprise Geodatabase).

Note: the procedure above requires an ArcGIS for Server license from ESRI. You can not complete this procedure without that.

* If trying to enable "Personal ArcSDE" a.k.a. "Desktop Geodatabase" a.k.a. "ArcSDE Personal Server" a.k.a "ArcSDE for SQL Server Express" on SQL Server Express edition:

- Look at the detailed instructions I provided here

Note: License for the limited(!) ArcSDE Personal Server is part of / included with the ArcGIS for Desktop "Standard" or "Advanced" license, so you don't need anything extra if you have either of these installed.

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

AsrujitSengupta — Mon, 16 Dec 2013 17:44:45 GMT

Also, if you are indeed using SQL Server Express Edition, make sure that it is 64-Bit as 32-Bit databases are not supported at 10.1.

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

El_BoukfaouiReda — Tue, 17 Dec 2013 02:44:29 GMT

I don't understand what you are trying to accomplish, either you run an enterprise grade SQL Server 2008 R2, or you run a limited SQL Server Express that only allows 10GB storage max.

What I am trying to do is creating a geodatabase under MS SQL Server 2008 R2 in my database server and making sure that it would be accessible by internal users in a company.
The architecture is a three-tier architecture (GIS Server, Database Server, Web Server)
My applications will run in the GIS Server, so my database need to be accessed by the GIS Server.
I am still confused regarding the SQL Server instance (ArcSDE for Microsoft SQL Server 2008 R2 Express (Personal) installation).

My question is the following : Where am I supposed to install ArcSDE ?

* If trying to create / enable a geodatabase on SQL Server 2008 R2 enterprise edition:
- Run the Create Enterprise Geodatabase or Enable Enterprise Geodatabase tools from ArcToolbox (whichever is relevant for your situation, since it seems you already created a 2008 R2 instance and database, it probably is Enable Enterprise Geodatabase).
Note: the procedure above requires an ArcGIS for Server license from ESRI. You can not complete this procedure without that.

Even when I am trying to 'Create Enterprise Geodatabase', it is asking for the SQL Server instance.
The only constraint as per my understanding is that the SQL Server instance is to be installed in the same machine(GIS Server) as ArcSDE, which is creating an issue for me as soon as the SQL Server instance is already created in another machine(database server)
Please kindly provide your feedbacks on that.

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

MarcoBoeringa — Tue, 17 Dec 2013 06:59:21 GMT

You still haven't answered my question conclusively: Are you using an enterprise grade SQL Server, or SQL Server Express??

The quote below now suggests you use SQL Server Express, is that right? Please answer this question, it is a vital distinction!:

I am still confused regarding the SQL Server instance (ArcSDE for Microsoft SQL Server 2008 R2 Express (Personal) installation).

As to the quote below:

My question is the following : Where am I supposed to install ArcSDE ?

You no longer need to install anything with 10.2. Direct Connect replaces the need for installing and running an ArcSDE Application Server, as in the 8.x,9.x era of ArcGIS. You just need to enable and license the geodatabase in an instance. Enabling means the ArcSDE Repository and its included ArcSDE and Geodatabase System Tables, including stored procedures etc., are added to a database in your instance. Once these are added and licensed, you can start using your ESRI Geodatabase.

ArcSDE functionality is an integral part of ArcGIS for Desktop and ArcGIS for Server. To better understand this, and since you seem to be confused about this all, I highly recommend you to read the PDFs about ESRI's Geodatabase Framework I created and posted here and here.

Even when I am trying to 'Create Enterprise Geodatabase', it is asking for the SQL Server instance.

As you now seem to be using SQL Server Express, you need to follow the instructions for enabling geodatabase functionality in your SQL Server Express instance I provided in this thread, under the heading for Personal ArcSDE / Desktop Geodatabase a.k.a. ArcSDE Personal Server.

The Create and Enable Enterprise Geodatabase tools can not be used against a SQL Server Express edition instance. They are for licensed enterprise geodatabases only, requiring a SQL Server enterprise edition as well.

Please note that an ArcSDE Personal Server will not allow more than 3 concurrent users. Otherwise, you need Workgroup or Enterprise licenses for ArcGIS for Server, to be able to use up to 10 (Workgroup license), or an essentially unlimited number of concurrent users (Enterprise license).

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

El_BoukfaouiReda — Tue, 17 Dec 2013 08:12:02 GMT

You still haven't answered my question conclusively: Are you using an enterprise grade SQL Server, or SQL Server Express??
The quote below now suggests you use SQL Server Express, is that right? Please answer this question, it is a vital distinction!:

I am using SQL Server 2008 R2 Enterprise Edition on the Database Server.
I am not using SQL Server Express.

You no longer need to install anything with 10.2. Direct Connect replaces the need for installing and running an ArcSDE Application Server, as in the 8.x,9.x era of ArcGIS. You just need to enable and license the geodatabase in an instance. Enabling means the ArcSDE Repository and its included ArcSDE and Geodatabase System Tables, including stored procedures etc., are added to a database in your instance. Once these are added and licensed, you can start using your ESRI Geodatabase.

Thank you for the information concerning 10.2.
I am still using at 10.1 products everywhere in my three-tier architecture.

As I claimed in the beginning,
I have a database server with MS SQL Server 2008 R2 Enterprise Edition and I need to put a centralized database in it, in order to make it accessible internally in a company by multiple users and mainly the GIS Server which will contain my GIS web applications which will use the geodatabase.
Please advice where to install 'ArcSDE for Microsoft SQL Server 2008 R2 Express (Personal)' (only ArcSDE software that I have through ArcGIS Desktop installation).Do I need to get another ArcSDE software?

Meanwhile, I am reading your tutorial to learn more about how we can proceed in this architecture...

Thank you.

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

MarcoBoeringa — Tue, 17 Dec 2013 09:04:42 GMT

I am using SQL Server 2008 R2 Enterprise Edition on the Database Server.
I am not using SQL Server Express.
...
Please advice where to install 'ArcSDE for Microsoft SQL Server 2008 R2 Express (Personal)' (only ArcSDE software that I have through ArcGIS Desktop installation).Do I need to get another ArcSDE software?

These two things are in conflict, you can not use the integral license of ArcGIS for Desktop Standard or Advanced, to enable a geodatabase in an enterprise class SQL Server instance. That limited ArcSDE Personal Server license only works for the also limited SQL Server Express.

Do I need to get another ArcSDE software?

You don't need extra software, you need a valid - and paid - license from ESRI for ArcGIS for Server, at the Workgroup or Enterprise level. You will need to buy / acquire this license through ESRI.

You than use that license in combination with the Create Enterprise Geodatabase, or Enable Enterprise Geodatabase tools to enable geodatabase storage on your enterprise level SQL Server instance.

Note that this doesn't necessitate installing ArcGIS for Server to launch webservices, you just need the license to enable your instance to store geodatabases (Although logically, once you have this license, you will probably want to run a webserver with GIS webservices too, and hence install ArcGIS for Server).

Thank you for the information concerning 10.2.
I am still using at 10.1 products everywhere in my three-tier architecture.

The Create and Enable Enterprise Geodatabase tools were introduced at 10.1, so you will be able to use those, e.g. see this Help page:

Create Enterprise Geodatabase (Data Management)

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

El_BoukfaouiReda — Tue, 17 Dec 2013 11:28:54 GMT

I am using 'ArcGIS Desktop Advanced Edition' which enables me to 'Create Enterprise Geodatabase'.

You than use that license in combination with the Create Enterprise Geodatabase, or Enable Enterprise Geodatabase tools to enable geodatabase storage on your enterprise level SQL Server instance.

We do have ArcGIS Server 10.1 Enterprise Edition (Concurrent use) license.
So, the procedure is to run 'Create Enterprise Geodatabase' in the GIS Server machine and enter the SQL Server Instance name of the Database Server SQL Server 2008 R2.

Let's say the Database server Name is : IPAdress\R2SQLDB1
The SQL Server instance name would be : IPAdress\DatabaseServerSQLServerInstanceName ??

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

AsrujitSengupta — Tue, 17 Dec 2013 13:08:41 GMT

How to find instance name:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/eb58a7fe-4647-4eea-91e3-6ee369362d64/how-to-find-instance-name?forum=sqlgetstarted

How can I determine installed SQL Server instances and their versions?:
http://stackoverflow.com/questions/141154/how-can-i-determine-installed-sql-server-instances-and-their-versions

SQL SERVER �?? Find Name of The SQL Server Instance:
http://blog.sqlauthority.com/2007/10/02/sql-server-find-name-of-the-sql-server-instance/

Easiest way: On the database server, Open the SQL Server Management Studio and verify from there.
[ATTACH=CONFIG]29936[/ATTACH]

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

MarcoBoeringa — Tue, 17 Dec 2013 15:40:58 GMT

I am using 'ArcGIS Desktop Advanced Edition' which enables me to 'Create Enterprise Geodatabase'.
...
We do have ArcGIS Server 10.1 Enterprise Edition (Concurrent use) license.

Good, this means you are basically "set" for creating or enabling an enterprise geodatabase. NOTE: If you haven't already done so, you may need to authorize ArcGIS for Server first to create the required keycodes / authorization file that is input for the Create and Enable Enterprise Geodatabase tools. See the Help pages for the Create and Enable Enterprise Geodatabase tools.

So, the procedure is to run 'Create Enterprise Geodatabase' in the GIS Server machine and enter the SQL Server Instance name of the Database Server SQL Server 2008 R2.

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.

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

VinceAngelo — Tue, 17 Dec 2013 16:16:05 GMT

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

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

El_BoukfaouiReda — Wed, 18 Dec 2013 11:28:41 GMT

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.

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

WilliamCraft — Wed, 18 Dec 2013 11:55:14 GMT

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.

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

VinceAngelo — Wed, 18 Dec 2013 11:58:20 GMT

"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

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

AsrujitSengupta — Wed, 18 Dec 2013 12:14:44 GMT

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

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

El_BoukfaouiReda — Thu, 19 Dec 2013 06:36:27 GMT

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

Re: Configure SQL Instance of a MS SQL Server Database from a GIS Server through ArcS

WilliamCraft — Thu, 19 Dec 2013 10:51:16 GMT

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