Sitecore 9 XP1 Installation with xDB on separate SQL Server

Question

We are in process of setting up the Sitecore 9.0.2 scaled environment for our upgrade from 7.2. On current production setup on 7.2 we have Analytics DB on a separate SQL Server than Core, Master, Web etc databases. Hence we want the same on Sitecore 9 as well. We will be having 1 CM, 1 Processing and 2 CD servers for the topology.

To achieve this Should I:

1. While installing vanilla 9.0.2, add a parameter in installation script to provide dedicated SQL server details for Analytics Dbs? If yes how to define these additional parameters and which all DBs of SC9 platform should be moved to that SQL server?
2. Or I just install the vanilla 9.0.2 in OOTB way, restore the analytics databases on a separate SQL server and update the connectionstring.config accordingly? If yes, just updating the connectionstring.config will suffice or I need to make additional tweaks as well?

Any help is much appreciated.

Answer 1

For installs where I have had to use SIF in a scaled environment, I have had to do some tricky installs. Maybe some of this can help:

First, I was able to locate a script that worked really well in removing the install for the database if you just want to install the web app and and then restore databases in your second step.

You can find that here:

https://www.robhabraken.nl/index.php/2740/blue-green-sitecore-deployments-on-azure/

Look under the section "Databaseless SCWDPs"

The other thing I will advise you on is that if you install the xDB databases on a different machine, restore them you will have to do the following:

1. change connection strings
2. Update the [__ShardManagement].[ShardsGlobal] table in the Xdb.Collection.ShardMapManager database. The [ServerName] field will need to be updated to the name of the new server.
3. You will need to configure shards manually - but use can use Sitecore's Sharp Map Manager Tool - please review this link for running it manually:

https://thebitsthatbyte.com/what-is-and-how-to-use-the-sitecore-9-shard-map-manager-tool/

Lastly, I think if you did want to go with option #1, you could possibly split your install up - meaning just isolate the xConnect install from the Sitecore install in its own file and change the applicable database server name where needed - similar approach here to what is being done with Solr:

https://tothecore.sk/2018/04/19/installing-sitecore-9-xp0-with-solr-on-remote-server/

I hope this helps.