Sunday 23 September 2012

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.
  DQCopies 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

Saturday 22 September 2012

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

 

Sunday 9 September 2012

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