Copying SQL Server settings to new server

We are a small business using a single SQL Server 2014 instance as our main data platform.

We now need to set up a second instance (2017) to hold additional data - this is to be standalone, ie not replicated or actively linked to the main server.

Disclaimer: I am a developer not a DBA. Using a trained DBA for this task is out of our budget.

I can happily install the software and try my best at setting it up correctly but ideally it would be great if there is a way to copy the majority of the settings from the main server to the new one.

We are happy with the way the main SQL instance is set up, is it possible to export and import these settings? Is there an easy to follow guide for creating a new SQL instance based on similar configuration of an existing one?

Solution 1:

The dbatools PowerShell module can do this for you. The module has a long list of commands, which can copy configurations, logins, databases--essentially an entire instance from one machine to another. You can choose to copy everything, or just a subset.

You probably want to at least copy configurations, DB Mail config, and probably some or all of your SQL Agent Jobs. Jobs may be an important thing to copy because they likely contain backup & other maintenance jobs that are going to be critical to your server health.

From the PowerShell prompt, you would do something like this--Note, I've included a few different examples for copying SQL Agent jobs:

# Install the module
Install-Module dbatools

# Import the module
Import-Module dbatools

# Copy configurations
Copy-DbaSpConfigure -Source "OldServer" -Destination "NewServer"

# Copy DB Mail
Copy-DbaDbMail -Source "OldServer" -Destination "NewServer"

# Copy All SQL Agent Jobs
Copy-DbaAgentJob -Source "OldServer" -Destination "NewServer"

# Copy a single SQL Agent Job
Copy-DbaAgentJob -Source "OldServer" -Destination "NewServer" -Job "Weekly Backups"

# Copy everything except two SQL Agent Jobs
Copy-DbaAgentJob -Source "OldServer" -Destination "NewServer" -ExcludeJob "Job1", "Job2"

Solution 2:

There is no real way to automate setting instance configuration settings that I am aware of without maybe some 3rd party tools, however, you could use this query, capture the results, then make the configuration changes on the new instance once installed to match manually via SSMS or T-Sql using those same settings.

Select * from sys.configurations order by name;