SPSearchServiceApp should support multiple database servers

[BrianFarnhill]

Details of the scenario you try and problem that is occurring:

Currently the SPSearchServiceApp only supports creating the databases on a single SQL Server. For very large scale deployments this might not be the desired state, so we should be able to configure the resource to move the DBs once they are provisioned.

The DSC configuration that is using the resource:

Any configuration of SPSearchServiceApp

Version of the Operating System and PowerShell the DSC Target Node is running:

n/a

Version of the DSC module you're using:

1.0

[camiloborges]

@BrianFarnhill Brian, can you please elaborate a bit more on this?

You mean FailoverServer, from New/Set-SPEnterpriseSearchServiceApp? or New-SPEnterpriseSearchCrawlDatabase Add-SPServerScaleOutDatabase

?

[BrianFarnhill]

None of those things actually - I'm thinking that the scenario here is that when you run New-SPEnterpriseSearchServiceApplication all four databases sit on the one instance of SQL. I've had a customer request come through to support moving the individual databases to different instances of SQL server to allow them to scale out (the size of the search farm they have in mind is pretty epic and IO of SQL is a concern).

I've done this manually before (based on whats at https://technet.microsoft.com/en-us/library/jj729803.aspx) but the biggest issue about this is actually going to be the database backup/restore to move it to the second sever. The rest is a lot of moving parts but not super complicated ones. My thinking here though is that we write the DB code to be nice and generic and then we can re-use that to be able to relocate any database (service apps, content DBs, etc) which would be a great improvement to how we manage existing databases.

So in my mind due to the size and complexity of this I would probably prioritise it down the list a little given that people aren't screaming for it (happy to be proven wrong here) but I do absolutely want it on the backlog.

[ykuijs]

Wouldn't it also be an good addition to change the resource to allow the databases to be created directly on the correct DB server and preventing this issue all together??

Which does not mean we do not need the above change, since insights about your environment can change and you need to perform such a db server move at a later time anyways.

[BrianFarnhill]

@ykuijs It would but New-SPEnterpriseSearchServiceApplication doesn't allow you to set parameters for each individual database, it just takes "databasename" and "databaseserver", then it suffixes the names of the databases for you and puts them on the one server. So this means we need some logic about the moves to make this work.

[ykuijs]

That sucks big time! This backup/restore procedure requires you to use an account with significant permissions on SQL in order to be able to do this....according to the TechNet article: "Verify that the user account that you are using to restore the databases is a member of the SQL Server sysadmin fixed server role on the database server where each database is stored".

Although the following article suggests dbcreator on instance level and db_owner on database level: https://msdn.microsoft.com/en-us/library/ms186858.aspx

[johlju]

It's gonna need permission in SQL instance to create logins, restore database. These permissions are normal when setting up SharePoint (for the setup account).

This idea with moving database are focusing on two or more standalone SQL instances, right? Wouldn't it be easier to support this only with AlwaysOn instead? If the service application, like Search, is setup using one AG listener then it might be easier to change the database server name to a second AG listener and move that database into that AG Group. That AG group can then be moved to a another active node in the AG cluster. This might be easier since then we can leave to the SQL DSC resource be to do that AG configuration and movement. The SharePoint DSC resource just need to change the database server name.
Is that an option?

[BrianFarnhill]

@johlju Yes and no - this could work but it introduces some additional complexities

1. You need enterprise SQL licenses to use AGs (although I believe this is different in 2016)
2. There is a limit to the number of AGs you can have on one server (I recall hearing 10 somewhere but again I need to check my numbers)

This means that while using individual availability groups would give you a way to achieve the initial goal - it's not likely going to scale or even be doable without bigger licensing costs.