SQL Server 2012 Silent Unattended Installation

Question: Many times we have installed SQL Server 2012, 2008 & 2005 using the traditional UI Mode, how can we install the same using Silent Install Mode.


Solution: In this is post we will be installing a 2012 SQL Server Named Instance (DR) using the Silent Mode from command prompt with a configuration file.

We will install the below features as part of this post.

Parent feature parameter	Feature parameter	Description
SQL		Installs the SQL Server Database Engine, Replication, Fulltext, and Data Quality Server.
	SQLEngine	Installs just the SQL Server Database Engine.
	Replication	Installs the Replication component along with SQL Server Database Engine.
	FullText	Installs the FullText component along with SQL Server Database Engine.
	DQ	Copies the files required for completing the Data Quality Server installation. After completing SQL Server installation, you must run the DQSInstaller.exe file to complete the Data Quality Server installation. For more information, see Run DQSInstaller.exe to Complete Data Quality Server Installation. This also installs SQL Server Database Engine.

We can change the configuration file according to our required services and features.

Below link will be useful to check the SQL Server Installation Parameters and other related stuff.

http://msdn.microsoft.com/en-us/library/ms144259.aspx

Step 1 : Prepare a configuration File for SQL Server Installation

Save the below parameters in a text file as "ConfigurationFile_Silent_Named_Instance.ini", change/add parameters as per you environment requirement. As per testing requirement I am only installing SQL Server Database Engine Features (Named Instance), you can check above link and add all SQL Server Shared Features if the same are not installed on the box.

[OPTIONS]
ACTION="Install"
ENU="True"
;UIMODE=Normal
IACCEPTSQLSERVERLICENSETERMS="TRUE"
;QUIET="FALSE"
QUIETSIMPLE="TRUE"
UpdateEnabled="False"
FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ
UpdateSource="MU"
HELP="False"
INDICATEPROGRESS="False"
X86="False"
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
INSTANCENAME="DR"
INSTANCEID="DR"
SQMREPORTING="False"
ERRORREPORTING="False"
INSTANCEDIR="I:\Microsoft SQL Server"
AGTSVCACCOUNT="NT Service\SQLAgent$DR"
AGTSVCSTARTUPTYPE="Automatic"
COMMFABRICPORT="0"
COMMFABRICNETWORKLEVEL="0"
COMMFABRICENCRYPTION="0"
MATRIXCMBRICKCOMMPORT="0"
SQLSVCSTARTUPTYPE="Automatic"
FILESTREAMLEVEL="0"
ENABLERANU="False"
SQLCOLLATION="Latin1_General_CI_AI"
SQLSVCACCOUNT="NT Service\MSSQL$DR"
SQLSYSADMINACCOUNTS="murali-HP\murali"
SECURITYMODE="SQL"
SAPWD="sapassword"
ADDCURRENTUSERASSQLADMIN="False"
TCPENABLED="1"
NPENABLED="0"
BROWSERSVCSTARTUPTYPE="Automatic"
FTSVCACCOUNT="NT Service\MSSQLFDLauncher$DR"

Step 2 : Start the SQL Server setup.exe from command prompt with the configurationfile option.

G:\Software\SQL Server 2012\SQLFULL_x64_ENU>setup.exe /CONFIGURATIONFILE="I:\SQL Server Own Material\scripts\Configurati
onFile_Silent_Named_Instance.ini"


After executing the above command SQL Server Installation will be in progress and the above command will be completed once the installation id done.

Step 3 : Check the related logfiles of the installation typically located in "C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log" and access the newly installed SQL Server

Script to find the Database Name and Status

Database Info

select instance_name,open_mode,flashback_on from v$database;

Session Deatils connected to SQL Server

We can use the below script to find the current sessions details which are connect to SQL Server 2012

 

Basic Deatils about the sessions connected to SQL Server:

select

des.session_id,der.status,des.login_name,db_name(des.[database_id]) dbname,

des.login_time,der.command,(select text from master.sys.dm_exec_sql_text(der.sql_handle)) sql_text,

der.percent_complete,der.wait_resource,der.wait_type,der.wait_time/1000 wait_time_sec,

des.host_name,des.program_name,des.client_interface_name,

des.cpu_time,des.memory_usage*8 memory_usage_KB,

des.reads,des.writes,des.row_count,

des.original_login_name,des.last_request_start_time,des.last_request_end_time,des.total_elapsed_time,des.total_scheduled_time

from

master.sys.dm_exec_sessions as des left join master.sys.dm_exec_requests der

on des.session_id=der.session_id

where is_user_process=1

and des.session_id<>@@spid

order by der.status desc

 

Detailed Info about the sessions connected to SQL Server:

select

des.session_id,task_state,der.status,des.login_name,db_name(des.[database_id]) dbname,

dos.scheduler_id,dos.cpu_id,dos.current_tasks_count,dos.current_workers_count,dos.runnable_tasks_count,

des.login_time,der.command,(select text from master.sys.dm_exec_sql_text(der.sql_handle)) sql_text,

der.percent_complete,der.wait_resource,der.wait_type,der.wait_time/1000 wait_time_sec,

des.host_name,des.program_name,des.client_interface_name,

des.cpu_time,des.memory_usage*8 memory_usage_KB,

des.reads,des.writes,des.row_count,

des.original_login_name,des.last_request_start_time,des.last_request_end_time,des.total_elapsed_time,des.total_scheduled_time

from

master.sys.dm_exec_sessions as des left join master.sys.dm_exec_requests der

on des.session_id=der.session_id

left join master.sys.dm_os_tasks dot

on des.session_id=dot.session_id

left join master.sys.dm_os_workers dow

on dot.task_address=dow.task_address

left join master.sys.dm_os_schedulers dos

on dow.scheduler_address=dos.scheduler_address

where is_user_process=1
and des.session_id<>@@spid

order by task_state desc

 

SQL Server Databases size usage details

Set nocount on

Declare @cmd varchar(1000)

set @cmd=' USE [?]; SELECT getdate() Report_Date , db_name() DBName,a.type_desc File_Type,

a.file_id as [FileId],

a.name as [LogicalName],

CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)) as [FileSize(MB)],

CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'''

+'SpaceUsed'+''')/128.000,2)) as [SpaceUsed(MB)], CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'''

+'SpaceUsed'+'''))/128.000,2)) as [FreeSpace(MB)], round((CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'''

+'SpaceUsed'+''')/128.000,2))/ CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))*100,2) as [Pct_SpaceUsed],

round((CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'''

+'SpaceUsed'+'''))/128.000,2))/ CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))*100,2) as [Pct_FreeSpace]

FROM sys.database_files a LEFT OUTER JOIN sys.data_spaces b

ON a.data_space_id = b.data_space_id'

Create table #DBFilesDetails(Report_Date datetime,DBName varchar(200),File_Type varchar(20),FileID int,

LogicalName varchar(400),[FileSize(MB)] decimal(20,2),[SpaceUsed(MB)] decimal(20,2), [FreeSpace(MB)]

decimal(20,2),[Pct_Spaceused] decimal(20,2),[Pct_FreeSpace] decimal(20,2))

insert #DBFilesDetails EXEC sp_MSforeachdb @cmd

select * from #DBFilesDetails

Drop Table #DBFilesDetails

Table usage script for SQL Server Database

set nocount on

DECLARE @tablename VARCHAR(250)

DECLARE db_cursor CURSOR FOR 

select table_schema+'.'+table_name

from information_schema.tables

where table_type='BASE TABLE'

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @tablename  

WHILE @@FETCH_STATUS = 0  

BEGIN  

          DECLARE @sqltableusage

          TABLE(name varchar(250),rows int,reserved varchar(20),

          data varchar(20), index_used varchar(20), unused varchar(20))

          insert into @sqltableusage

          Exec sp_spaceused @tablename 

       FETCH NEXT FROM db_cursor INTO @tablename  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

--select * from @sqltableusage

select name,rows,replace(reserved,'KB','') Reserved_KB,replace(data,'KB','') Data_KB,

replace(index_used,'KB','') Index_Used_KB,replace(unused,'KB','') Unused_KB,

convert(decimal(14,2),(replace(reserved,'KB','')))/1024 as Reserved_MB,

convert(decimal(14,2),(replace(data,'KB','')))/1024 as Data_MB,

convert(decimal(14,2),(replace(index_used,'KB','')))/1024 as Index_Used_MB,

convert(decimal(14,2),(replace(unused,'KB','')))/1024 as Unused_MB

from @sqltableusage