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 create a Logon Trigger to control which users are allowed to connect to an Oracle Database - 13 January 2025
• How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
• How do we find what permissions a user has on an Oracle database - 1 November 2024

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