export all SQL server setup configuration to a new SQL server

Dear all,

as I am going to setup a new bunches of new SQL server and we can copy the same configuration from one of the existing SQL server, any way to export and import such kind of configuation from existing SQL server?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marrowyungSenior Technical architecture (Data)Author Commented:
0
Deepak ChauhanSQL Server DBACommented:
You have to update it manually using sp_configure system stored procedure.
0
marrowyungSenior Technical architecture (Data)Author Commented:
what is that mean ? I don't understand, so can't directly export configuration and import it to another SQL server ? so we have copy the setting manually ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Deepak ChauhanSQL Server DBACommented:
There are two types of configuration in sql server.

1. configuration during installation  like database file location, service accounts , Authentication settings (Windows\ SQL) , Temp DB placement, collation. etc...

You can generate configuration file during installtion and use same file for all installation.
https://msdn.microsoft.com/en-us/library/dd239405(v=sql.105).aspx

2. Post installation configuration like Min \ Max memory settings, MaxDOP, affenity Mask ... etc.

All setting are saved in system tables and you can not copy to another instance , manuall update require.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
how about I already go through installation wizard as I cant' wait for your answer, anyway to import the setting after the installation wizard ? I am now installing the SP2
0
Deepak ChauhanSQL Server DBACommented:
The above link is explaining the way to create a configuration file and use it in other installation.
0
marrowyungSenior Technical architecture (Data)Author Commented:
I mean before you reply that I already finish installing the rest ! so I don't use that configuration INI file to install it at all ! but a lot of configuration like tempdb configuration, to the new installed SQL server DB.

is it possible to generate the configuration from existing SQL server to the new installed SQL server which is INSTALLED ?
0
Deepak ChauhanSQL Server DBACommented:
Yes, you can copy it from here

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\<installationData>\ConfigurationFile.ini

100 for (SQL2008) and 110 for SQL2012.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Yes, you can copy it from here "

where I copy it to and how can I apply it AFTER I INSTALL the SQL server 2008 R2 ?
0
Deepak ChauhanSQL Server DBACommented:
Configurationfile.ini can be used only during installation not after installation. This is only for setting up the dataabse default  If you are taking about post installation configuration then you have to
1.execute SP_configure and check the value in result .
Or
2. select * from sys.configurations


For eg: You want to enable "allow updates" confoguration option in this case you have to run

Sp_configure "allow updates"  , 1
go
Reconfigure

This is the manual process or you can make a script manually and execute it on all instances.
0
marrowyungSenior Technical architecture (Data)Author Commented:
wait, what is  "allow updates" configuration for ?

"Configurationfile.ini can be used only during installation not after installation. This is only for setting up the dataabse default  If you are taking about post installation configuration then you have to "

that's what I am worrying about !

"1.execute SP_configure and check the value in result .
Or
2. select * from sys.configurations
"

you mean once run this, copy the setting one by one ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Which kind of configuration do you wish to be set in all SQL Server?
Usually we have a setup configuration that is used to install all SQL Server instances but then each instance has their own resource configuration (CPU, Memory, disk, MAXDOP, ...) depending on the databases that will be stored.
0
marrowyungSenior Technical architecture (Data)Author Commented:
oh, I have gave a task to install SQL server and the configuration will be the same as one of the SQL server, which serve the same purpose.

as I need to export all configuration except server name from this server to all SQL server I am going to install.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
"as I need to export all configuration except server name from this server to all SQL server I am going to install."

Please define ALL CONFIGURATION
0
marrowyungSenior Technical architecture (Data)Author Commented:
oh, yeah. it is hard to define now .
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.