SqlSetup: The resource should support the action REBUILDDATABASE

[ChrisLGardner]

If SQL is already installed on a machine (such as Azure images) then using xSqlServerSetup to change any of the other configurations other than Features doesn't do anything.

Should we extend xSqlServerSetup Test-TargetResource to include checking collation (and eventually all the other possible settings)?

Do we want to extend xSqlServerDatabase to also allow setting collation that isn't the same as the server level one.

I'm happy to work on a PR for the xSqlServerSetup section.

[johlju]

We had a discussion here where we discussed that if setup.exe can not do the change, then the resource xSQLServerSetup should not change those properties. This is open for debate, though. I don't want a new resource for every setting/property, so if changing collation for the server is not part of xSQLServerSetup, then maybe changing collation should be in a resource called something like xSQLServerSettings?

Sounds good extending xSQLServerDatabase to change collation for a database. Please open a separate issue for that though.

[ChrisLGardner]

The current method I'm using is a Script block that just calls Setup.exe and gives it the collation to use so I think it's appropriate for the xSQLServerSetup resource.

I'll look at how best to test the collation is correct as that should be the only change that's needed, the rest of the functionality is already there for applying the collation.

[johlju]

Cool. Sounds great. I label this as "in progress" then.

[ChrisLGardner]

I did some digging into this and it doesn't look like setup.exe can do it as expected. There is an alternate approach but that drops all other databases than the system ones so not really appropriate for this. I think this can be closed and I'll try to look at setting collation on individual databases in it's own resource or as a extension to another resource.

[johlju]

As mentioned above. Please submit a new issue to extend the xSQLServerDatabase resource. Looking forward to a PR for xSQLServerDatabase that supports changing collation! 😄

Closing this issue since this was related to server collation, and it seemed it wasn't practical to change it.

[flouwers]

Changing the collation can be done by launching the setup.exe with "/REBUILDDATABASE" switch.
However, this command is not implemented in the DSC module. Could that be added in a future release ? (BTW I already implemented this workaround)

[johlju]

@flouwers That action will destroy any user databases currently on the system. But it do have the ability to Set or Change the Server Collation. If someone wants to implement this and send in a PR, then sure I'm happy to merge it so we get this functionality into a future release. 😄

@flouwers Are you up to extending xSQLServerSetup (with code and unit tests) it if you already have code for it? In the mean time I label this one as help wanted until someone wants to run with it. 😄

Changing and reopening this issue title to add support for action REBUILDDATABASE.

[johlju]

Reopening this issue with a new issue title since this will not only enable changing collation, but could potentially be used for other scenarios.