How do we transfer server configurations from one SQL Server Instance to another
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:
Certifications:
Latest posts by Stratos Matzouranis (see all)
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
