Monday, June 20, 2016

Install SQL Server using configuration file?

SQL Server allows to generate configuration file and using this file we can deploy sql instance with same configuration for different server. We can also standardize manual for installing sql with batch files which will launch SQL setup.

Setup supports the use of the configuration file only through the command prompt. The configuration file overwrites the defaults in a package and Command-line values overwrite the values in the configuration file

The configuration file can be used to track the parameters and values for each installation. This makes the configuration file useful for verifying and auditing the installations.

The ConfigurationFile.ini file is a text file with parameters (name/value pair) and descriptive comments.
To generate configuration file we need to start setup using media (setup.exe) till ready to install page.
The path to the configuration file is specified in the Ready to Install page in the configuration file path section.
Then cancel the setup,

Normally file location would be : File Location and Name: %programfiles%\Microsoft SQL Server\110\Setup Bootstrap\Log\\ConfigurationFile.ini.
To see the progress of installation during setup using ConfigurationFile.ini we can manually edit the file and set the parameter INDICATEPROGRESS to TRUE and QUITESIMPLE to TRUE.
NB: In ConfigurationFile.ini sa password not be included even if you have entered while creating file, we must add parameter SAPWD=”Password” below SECURITYMODE=SQL in file to include sa password or we can add later after installation done.

To install the SQL=>
Copy ConfigurationFile.ini to setup folder where setup.exe is and use command prompt to use syntax from setup folder:
ð  Setup / ConfigurationFile= ConfigurationFile.ini
To specify passwords at the command prompt instead of in the configuration file:

ð  Setup.exe /SQLSVCPASSWORD="*****" /AGTSVCPASSWORD="*****" /ASSVCPASSWORD="*****" /ISSVCPASSWORD="*****" /RSSVCPASSWORD="******”  /SECURITYMODE=SQL /SAPWD ="******” /ConfigurationFile=MyConfigurationFile.INI

Tuesday, June 14, 2016

Page File for SQL Server

Page File for SQL Server

Page file is space allocated on system drive as a file and its functionality is: operating system works with memory and paging file. The page file is located on the disk, and when there is not enough physical memory left, operating system can use the page file and move data from the physical memory to it. When it will need that data, it will have to read it from the disk. The problem is that disk is a lot slower than the physical memory. If SQL Server's cache will be stored on the disk instead of the on the physical memory, you'll have a huge performance problem.

In general page file is physical extension of RAM and if not enough memory left during process page files save server from crash or being hung out.

Maximum memory space required by windows commit limit will be: RAM size + page file size

Normally a DBA set page file to 2X of RAM size which is not always correct and it depends over server functionality. For 32 bit size maximum page size should be 4 GB.

But for modern server we should have less page file size if RAM size is more, we can use performance counter to know the value of page file.

Create counter Memory\Committed Bytes and set it for one week and once we have data calculation would be

Max value if committed byte over period (20 GB) + 30% of committed byte as buffer – RAM Size.
For 16 gb Ram = data would be 20+6-16 = 10 gb pf page file
+  we also need to consider kernel or memory dump size which also store data primarily on page file before moving it to specified dump folder.

From Windows 2008 and onward we can put page file on any partition, not only system partition as before 2008. If page are created along multiple drives sql will generally use page file from faster drive.

To configure page file setting: go to:
 system->advanced system setting->advanced->setting under performance tab->Advanced