sql server database location for enterprise

[AndyIngall]

Hello

Just looking for thoughts or ESRI's comments on the location of the enterprise geodatabase within a SQL server.  Is it recommended that the GIS database be located on its own SQL server or can it be located within a SQL Server containing many databases - what is the best or preferred method??

Thanks

[VinceAngelo]

There isn't really an answer for this, and asking for an Esri response from the user forums isn't the right approach -- Even if employees do respond, they aren't speaking for the corporation (and asking them to actually reduces participation).

That said, the recommendation is to use a server adequate to the task. Is that a standalone server? Well, maybe. But give the DBA the benefit of the doubt, since requiring that they add a box they don't want to use (and that has to be paid for) isn't likely to make their life easier.

In the end, the "best" solution is the one that works for everybody. If the shared server is overloaded, adding GIS to the mix isn't going to help, but you'll be able to benchmark that. Sometimes everything seems fine in the TEST system, but moving into PRODUCTION demonstrates a scaling issue, even with a standalone server. And increasing the number of servers doesn't always improve performance, either, because sometimes the network or the SAN is the bottleneck. Each deployment is different, and can have wildly varying "best" solutions.

- V

[RyanUthoff]

There really isn't a right answer to this question. The "best" answer is placing the EGDB on a SQL Server instance that has enough resources to meet demand AND has good connectivity to all GIS users who will be interacting with it (whether it be through ArcGIS Pro, Enterprise, etc.).

There are no issues with mixing non-EGDBs and EGDBs together on the same SQL Server. What matters is that the SQL Server has enough resources to meet the demand.

[Brian_Wilson]

Do you have to support a little county or a big country?

Short answer - everything ran fine on one virtual machine but it's easier to maintain on multiple machines.

Long answer - Our small county (40,000 pop) started with everything on one virtual machine - server, portal, datastore, web adaptors, IIS, and MS SQL. It worked fine, especially since we were not running any web apps then. The problem with having SQL Server in there was that I had to maintain it. We moved our database to the county-wide instance and got high availability (they have 2 or 3 servers, I forget), IT maintenance (updates and backups and moral support as needed). Since their servers are sized to handle the entire county's needs and ONLY run SQL Server, our puny spatial data needs had no impact. We also benefit from not being limited to 4 cores over there.

Then we moved each ESRI component to a separate virtual machine. So now each has 4 cores of its own, still complying with our license but giving us more than 12 cores instead of 4. (4 each for datastore, portal, and server plus the ones on the SQL Servers) IIS (and web adaptors) are still on the Portal machine.

Hope this gives you some useful info,

Brian

[Brian_Wilson]

We have this fantastic SQL Server machine but whenever I publish data as "hosted", it puts it on the relatively underpowered Data Store machine and we don't have any say in that.

It works fine for us, I just find it to be ironic. I'd kind of like to say where my data gets stored. I bet there is already an Idea for this, I think I will go find it and upvote it.

[DavidHoy]

Hi Brian - the solution for your wish to decide where the data is stored is already there.
If you choose to publish a Hosted Feature Service, then the data will be copied automatically tothe system managed Relational Data Store (as you have seen).

If you wish to publish a non-Hosted Map Service (with Feature Access enabled) you have a couple of choices.

1. check the "copy data" option in the "share" dialog
   this will force the source datasets to be copied to the selected ArcGIS Server site (in your case I think you only have a single ArcGIS acting as the Hosting Server role)
   This will make the system copy the source datasets to the arcgisserver\directories\arcgissystem\arcgisinput\<servicename>\extracted\p20 directory.
   This is not an generally an optimal practice - as the copied data does not get updated as your original source data changes.
2. Register you Enterprise Geodatabase as a database type data store
   (See Manage registered data stores—ArcGIS Pro | Documentation)
   Since ArcGIS Enterprise 10.7.1 - when you register data store with ArcGIS Pro or via the Portal web page, the are created as items in your Portal - and you will see them in Server Manager, but not be able to a configure these from Server Manager. If you use Server Manager to register data store - they won't show as items in your Portal.
   Once you have a registered data store and your source data is in that location (it could be a fileshare or an Enterprise Geodatabase), if your Pro map sources the data from that location (and uses the same access details (common .sde connection file say), you can opt to "use Registered Location" when publishing a non-hosted map service.
   This will mean your data is not copied redundantly and the latest data will always be being served up by your service.
3. another option, if you do want data to be copied to the Enterprise Geodatabase during publication - you could add a separate ArcGIS Server site, register the Geodatabase with that Server and configure that registered data store as the "managed database". This allows use of the "copy data" option while publishing to that Server, but the copied data will go to the managed database rather than to the Server's "arcgisinput" folder.
   See this post for further info:
   Solved: About managed database in Server - Esri Community