How do we transfer server configurations from one SQL Server Instance to another

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How to roll back an Oracle Database using a restore point in a Data Guard environment - 28 April 2025
• How can we increase performance on Oracle GoldenGate Replicat target with parallelism? - 19 March 2025
• How to create users in databases that belong to a SQL Server Always On Availability Group - 10 February 2025

In the article we will see an easy way to transfer the parameters of one SQL Server instance in another. Parameters like max memory, max degree of parallelism, cost of threshold for parallelism …

All we'll need to do is run the T-SQL script I've made on the server that has the parameters we want to keep.

The code

select 'sp_configure ''show advanced options'',1'
union all
select 'reconfigure with override'
union all
select 'go'
union all
select
'sp_configure ''' + cast(name as nvarchar(255)) + ''',' + cast(value as nvarchar(255)) + '' + ';'
from sys.configurations
union all
select 'go'
union all
select 'sp_configure ''show advanced options'',0'
union all
select 'reconfigure with override'
union all
select 'go'

The result

We can copy the generated commands all together to execute them on the server we want to have the same parameters.

Do the parameters take effect immediately or does the instance need to be restarted?

Most parameters work dynamically, i.e. they don't need to restart the instance to activate.

To see which ones are not and require a restart, we execute the following query.

SELECT name ,description FROM sys.configurations
WHERE is_dynamic = 0