Install SQL Server 2008R2 With ConfigurationFile.(Configuration.ini)

Install SQL Server 2008R2 With ConfigurationFile.(Configuration.ini)

 Step 1:-

Run Sql Server 2008 R2 Set Up .

Step 2:-

Run the Sql Server Setup untill Ready To Install option not shown and copy the path of Configuration File(Configuration.INI) and click on the cancel installation.

Step :3

 Edit the configuration file as follows:

1.       Set QUIET to “True”. This specifies that Setup will run in a quiet mode without any user interface

i. QUIET="True"

2.       Set SQLSYSADMINACCOUNTS to “BUILTINADMINISTRATORS”. This will ensure that administrators on the machine are added as members of the sysadmin role. You can set its value based on your needs (Ex: SQLSYSADMINACCOUNTS=”domainYourUser”), but this is the more generic approach. I have added My 

user instead of BUILTINADMINISTRATORS, to secure SQL server from unwanted logins.

i. SQLSYSADMINACCOUNTS="BUILTINADMINISTRATORS" or local system name

3.       Add PID and set its value to your product license key. If your setup.exe already comes preloaded with the key, there is no need to add this option to the configuration file.

4.       Add IACCEPTSQLSERVERLICENSETERMS and set its value to “True”. This is to require to acknowledge acceptance of the license terms at time of unattended installations.

i. IACCEPTSQLSERVERLICENSETERMS="True

5.       Remove the ADDCURRENTUSERASSQLADMIN parameter. The reason is that this parameter can’t be used when SQLSYSADMINACCOUNTS is specified, and it only applies to Express installations.

6.       Remove the UIMODE parameter as it can’t be used with the QUITE parameter.

7.       Remove INSTALLSHAREDDIR, INSTALLSHAREDWOWDIR, INSTANCEDIR parameters if you want to install on the default installation directories or mention appropriate directories for installation.

8.       You can add or remove the feature you want to install, Select FEATURES=SQLENGINE,SSMS,ADV_SSMS in the configuration file. You can change that based on your needs.

9.       The full list of available feature parameters and their descriptions : http://msdn.microsoft.com/en-us/library/ms144259.aspx#Feature

10.   Now, Your configuration file is ready, you need to create a batch file that will run the silent unattended setup. Create a new file ”SQLServer2012_SilentInstall” with extension = “.bat”.

We have added, Date time to get the time taken by complete installation. Edit below script with your setup & configuration file location.

@ECHO offecho Installing SQL Server 2008 R2date/ttime /t “D:SQLFULL_x86_ENUsetup.exe” /ConfigurationFile=”D:ConfigurationFile.ini” date/t time /t save this into .BAT file and run

MY EDIT Configration.ini file For Sql Server 2008R2

==================================================================

;SQLSERVER2008 Configuration File

[SQLSERVER2008]

; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will reflect the instance ID of the SQL Server instance. 

INSTANCEID="SQL2008Rcmd"

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. 

ACTION="Install"

; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, and Tools. The SQL feature will install the database engine, replication, and full-text. The Tools feature will install Management Tools, Books online, Business Intelligence Development Studio, and other shared components. 

FEATURES=SQLENGINE,REPLICATION,FULLTEXT

; Displays the command line parameters usage 

HELP="False"

; Specifies that the detailed Setup log should be piped to the console. 

INDICATEPROGRESS="False"

; Setup will not display any user interface. 

QUIET="True"

SAPWD=”access”

; Setup will display progress only without any user interaction. 

QUIETSIMPLE="False"

; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system. 

X86="False"

; Detailed help for command line argument ENU has not been defined yet. 

ENU="True"

; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature. 

ERRORREPORTING="False"

; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature. 

SQMREPORTING="False"

; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS). 

INSTANCENAME="SQL2008Rcmd"

; Agent account name 

AGTSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"

; Auto-start service after installation.  

AGTSVCSTARTUPTYPE="Automatic"

; Startup type for Integration Services. 

ISSVCSTARTUPTYPE="Automatic"

; Account for Integration Services: Domain\User or system account. 

ISSVCACCOUNT="NT AUTHORITY\NetworkService"

; Controls the service startup type setting after the service has been created. 

ASSVCSTARTUPTYPE="Automatic"

; The collation to be used by Analysis Services. 

ASCOLLATION="Latin1_General_CI_AS"

; The location for the Analysis Services data files. 

ASDATADIR="Data"

; The location for the Analysis Services log files. 

ASLOGDIR="Log"

; The location for the Analysis Services backup files. 

ASBACKUPDIR="Backup"

; The location for the Analysis Services temporary files. 

ASTEMPDIR="Temp"

; The location for the Analysis Services configuration files. 

ASCONFIGDIR="Config"

; Specifies whether or not the MSOLAP provider is allowed to run in process. 

ASPROVIDERMSOLAP="1"

; A port number used to connect to the SharePoint Central Administration web application. 

FARMADMINPORT="0"

; Startup type for the SQL Server service. 

SQLSVCSTARTUPTYPE="Automatic"

; Level to enable FILESTREAM feature at (0, 1, 2 or 3). 

FILESTREAMLEVEL="0"

; Set to "1" to enable RANU for SQL Server Express. 

ENABLERANU="False"

; Specifies a Windows collation or an SQL collation to use for the Database Engine. 

SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"

; Account for SQL Server service: Domain\User or system account. 

SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"

IACCEPTSQLSERVERLICENSETERMS="True"

; Windows account(s) to provision as SQL Server system administrators. 

SQLSYSADMINACCOUNTS="GHOST-PC\GHOST"

; The default is Windows Authentication. Use "SQL" for Mixed Mode Authentication. 

SECURITYMODE="SQL"

; Specify 0 to disable or 1 to enable the TCP/IP protocol. 

TCPENABLED="1"

; Specify 0 to disable or 1 to enable the Named Pipes protocol. 

NPENABLED="0"

; Startup type for Browser Service. 

BROWSERSVCSTARTUPTYPE="Automatic"

; Specifies how the startup mode of the report server NT service.  When 

; Manual - Service startup is manual mode (default).

; Automatic - Service startup is automatic mode.

; Disabled - Service is disabled 

RSSVCSTARTUPTYPE="Automatic"

; Specifies which mode report server is installed in.  

; Default value: “FilesOnly”  

RSINSTALLMODE="FilesOnlyMode"

; Add description of input argument FTSVCACCOUNT 

FTSVCACCOUNT="NT AUTHORITY\LOCAL SERVICE"

========================================================================

When was my database last used ??

Use <DatabaseName>
GO
SELECT DB_NAME() as DatabaseName,
s1.sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset

When was my table last scanned or updated?

When was my table last scanned or updated?
Hi i am posting a blog related to how to check when your table last updated or scanned. This issue occurred when more than one person work on same project and some some mistakenly changed by anyone.

USE DatabaseName  ----your database name--
GO
SELECT
t.name,
i.last_user_lookup,
i.last_user_scan,
i.last_user_seek,
i.last_user_update
FROM sys.dm_db_index_usage_stats i
INNER JOIN sys.tables t
ON i.object_id = t.object_id
WHERE
database_id = db_id( 'DatabaseName' )  ----your database name--

How could i insert the data into my secondary files(.ndf)?

Step 1:- Create a file group with secondary file (.ndf file)

( NAME = N'FileBackupsTest',
FILENAME = N'E:\FileBackupsTest.mdf' ,
SIZE = 5120KB , FILEGROWTH = 5124KB ),
FILEGROUP [SecondaryFile]
( NAME = N'FileBackupsTestUserData1',
FILENAME = N'e:\FileBackupsTestUserData1.ndf' ,
SIZE = 512KB , FILEGROWTH = 512KB )
LOG ON
( NAME = N'FileBackupsTest_log',
FILENAME = N'e:\SQLData\FileBackupsTest_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 512KB )
GO
USE [FileBackupsTest]
GO

Step 2: create a table with secondary file group name.By default a table created on Primary file (.mdf) file but if you want to enter data into secondory file(.ndf) than mention seondory File group name.
CREATE TABLE Demo
(
name char(12),
city char(12),
roll int
)
ON [secondaryFile]



Step : 3 insert some data into this table and check the size of your secondary file will grow in size. check the size with sp_helpdb 'FileBackupsTest1'
GO and To Find which object resides in which FileGroup in a database, run the below queries.


T-SQL - Find which Object resides in which FileGroup
To Find which object resides in which FileGroup in a database, run the below queries.

/**************** For SQL 2000 ****************/

Select Distinct OBJECT_NAME(SI.id) AS ObjectName
        ,SO.type AS ObjectType
        ,FG.GroupName AS FileGroupName
from sysindexes SI , sysfilegroups FG ,sysobjects SO
where SI.groupid = FG.groupid and
      SI.id = SO.id
Order by ObjectName

/**************** For SQL 2005 and SQL 2008 ****************/

Select Distinct OBJECT_NAME(SI.object_id) AS ObjectName
        ,OBJECTPROPERTYEX(SI.object_id,'BaseType') AS ObjectType
        ,FG.Name AS FileGroupName
from sys.indexes SI , sys.filegroups FG
where SI.data_space_id = FG.data_space_id
Order by ObjectName