SQL Server installation methods

There are three types of Installation methods 

1. Local/Standard installation

2. Unattended installation 

3. Remote Installation 

Local/Standard installation

To perform standard installation you need to login into the machine and the login is part of local administrator group. 

Unattended installation 

It is also known as silent installation. Install multiple SQL Servers with identical configurations without using the interactive SQL Server setup is known as Unattended installation. 

Installation initiates from command prompt ( batch file will call setup file).

All the parameters are saved in configuration file (setup.exe) 

An unattended installation using a configuration file using the following command line at a new command prompt.

D:\SQLFULL_x86_ENU\Setup.exe /ConfigurationFile=D:\SQLFULL_x86_ENU\SQLConfigurationFile.ini

 

Remote Installation

You can install SQL Server from remote machine also. 

To perform remote installation the user account should have following permissions 

1. Have administrative rights on the remote computer. 

2. Have read access to the setup source files folder.

Rename the sql server database

There are couple of options are available to rename the database. 

Option 1 : 

 Rename the "MyDATABASE" to "MyDATABASE_NEW" using sp_renamedb command

 EXEC sp_renamedb 'MyDATABASE', 'MyDATABASE_NEW' 

Option 2:

 Rename the "MyDATABASE" to "MyDATABASE_NEW" using ALTER DATABASE Commnad

USE [master]

GO

ALTER DATABASE [MyDATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE [MyDATABASE] MODIFY NAME = MyDATABASE_NEW

GO

ALTER DATABASE [MyDATABASE_NEW] SET MULTI_USER

GO

SQL Script to Change the database to read only and read write

Below script is used to change the database into single user mode. To kill the current connections change the database to single user mode then change it to single user mode.Replace the database name from MyDATABASE to the database name need to change to READ ONLY

USE [master]

GO

ALTER DATABASE [MyDATABASE]  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

 ALTER DATABASE [MyDATABASE] SET READ_ONLY WITH NO_WAIT

GO

ALTER DATABASE [MyDATABASE] SET MULTI_USER

GO

Below script is used to change the database into read/write user mode. 

 ALTER DATABASE [MyDATABASE] SET READ_WRITE WITH NO_WAIT

SQL Server Job history

select job_name, run_datetime, run_duration

from

(

    select job_name, run_datetime,

        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +

        SUBSTRING(run_duration, 5, 2) AS run_duration

    from

    (

        select DISTINCT

            j.name as job_name, 

            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  

                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,

            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)

        from msdb..sysjobhistory h

        inner join msdb..sysjobs j

        on h.job_id = j.job_id

    ) t

) t

where job_name = 'Job name '

order by job_name, run_datetime

--- Query results are like below

Difference between DateTime and DateTime2 DataType

DATETIME2 is the new data type introduced in SQL Server 2008

DATETIME2 provides more seconds precision & has larger date range, a larger default fractional precision. DATETIME is limited to 3 1/3 milliseconds, while DATETIME2 can be accurate down to 100ns

Advantages of DATETIME2

DATETIME2 has a date range of "0001 / 01 / 01" through "9999 / 12 / 31" while the DATETIME type only supports year 1753-9999

Larger range of values

Better Accuracy

Smaller storage space (if optional user-specified precision is specified)

Precision, scale

0 to 7 digits, with an accuracy of 100nanoseconds.

The default precision is 7 digits.

Storage Size

6 bytes for precision less than 3 

7 bytes for precision 3 and 4.

All other precision require 8 bytes.

DATETIME2 with fractional seconds precision of 3 is same as DATETIME data type. And DATETIME2 (precision of 3) uses 7 bytes of storage instead of 8 byte

 If you only need the date without time part use DATE 

 If you only need the time without date part use TIME 

 Using DATE & TIME data types instead of DATETIME2 in above situations you save the space

Install SQL Server 2012 in Stand Alone Machine

Requirements for Installing SQL Server 2012:

 1.        Operating system Requirement :

You can install in below operating systems

• Windows Server 2012,
• Windows 8,
• Windows server 2008 R2 SP1,
• Windows 7 SP1.

You can also install in below operating systems but you require to install update http://support2.microsoft.com/?kbid=956250 on below operating system before initiating the installation.

• Windows Vista SP2
• Windows Server 2008 SP2

 2.       NET Framework

• .NET 4.0 is a requirement for SQL Server 2012. SQL Server installs .NET 4.0 during the feature installation step.
• You must enable or install.NET 3.5 SP1, If you select to install Database Engine, Reporting Services, Master Data Services, Data Quality Services, Replication, SQL Server Management Studio on Windows Vista SP2 or Windows Server 2008 SP2 operating systems. OS wont have .NET 3.5 SP1 Framework.Installing SQL 2012 to Microsoft® Server 2008 R2 requires that the Application Server role be added prior to running setup 

3.     Windows Powershell

• Powershell 2.0 is mandatory to install SQL Server 2012. If  SQL Server setup reports Powershell 2.0 is not present, you can install or enable it.

 4.        Internet Explorer 7 or a later version.

 5.        Hard Disk : SQL Server 2012 requires minimum of 6 GB.

 6.       Memory

      Minimum :  

•  Express : 512 MB
•  Other  : 1 GB

       Recommended

•  Express : 1 GB
•  Other  : 4 GB

Installation Steps:

Go to setup file location (or installation media) click on setup.exe

Initial window will show you Planning Menu screen

The planning menu contains help files that go over everything from System Requirements to Upgrade Documentation.

Click on Installation on the left pane to access the installation menu

In installation page, right side pane you will found 4 option 

               

Option 1 : New SQL Server stand-alone installation or add features to existing installation 

                                Using this option you can install Stand-alone server  or add SQL Server features to an existing installation

Option 2 : New SQL Server failover cluster installation

                                Using this option you can install a SQL Server in Failover Cluster

Option 3 : Add Node to the SQL Server failover cluster

                                This option is used to add a node to an existing failover cluster

Option 4 : Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2

                                Using this option we can upgrade the existing SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2 to SQL Server 2012

Now we are performing SQL Server stand-alone installation so click on Option 1.

It will initiate the stand-alone installation process of SQL Server 2012

Setup support rules runs a prerequisite check, these are Operating System checks. Rules and status of pass, failed, warning or skipped will be returned.

                               

Next screen displays you Setup Support Rules screen, click on  button to see all the rules detail information.

Once these rules are met then  Click on Ok

Above screen will provide you the available latest updates.

Click on the Next.

In next screen you need to select weather is it new installation are adding features to an existing installed instance.

Select Perform a new installation of SQL Server 2012 radio button for fresh installation

In next screen need to select weather is it free edition or need to provide product key and click on Next

Next  is the license  terms screen. You need to accept the license terms and click on Next.

l

INSTALLATION OPTIONS AND CONFIGURATION

Commonly used Components

Database Engine - Required

Replication - Optional

Full-Text Search – Optional

Analysis Services – Optional

Reporting Services – Optional

Business Intelligence Development Studio - Optional

Client Tools – Optional

Integration Services  – Optional

SQL Server Books Online  - Recommended

Management Tools - Basic - Required

Management Tools - Complete  - Required

SQL Client SDK – Optional

Select All features with defaults to install all features with default values and select Next

Select the what ever you want install in the instances. If this is the first installation you can select what ever shared features you want. If it is another instance already installed shared features are grayed out. Click on Next

Make changes to the root directory as per your company standards, otherwise accept defaults and choose next

Next screen will display the disk space requirements. 

Specify the accounts SQL Server processes will use to run.

Server Configuration provides the option to select 

Network Service 

System from the Account Name drop down.  

As per Microsoft recommendation use a new account for each these services. Otherwise use  'NT Authority\System'

Collation tab will show the collation settings information used to below purpose

• The code page that is used to store non-Unicode data in SQL Server.

• The rules that govern how SQL Server sorts and compares characters that are stored in non-Unicode data types. SQL Server Setup will detect the Windows collation on the computer where SQL Server is being installed. Then, it will automatically select the collation for your instance of SQL Server. Sorts and comparisons might be different for Unicode and non-Unicode characters.

 you can change the collation  that supports the Windows system locale most commonly used at your organization

Click on the Next 

Authentication Mode MUST be set to Mixed Mode. 

SA (SQL Server system administrator) should use a strong password even though the installer may not check it. 

Specify non-built in SQL server administrators.  For example: a domain administrator who is responsible for and should have full rights to the SQL server. 

Click Add Current user button if the currently logged in user should have rights to this tool.

In Data Directories tab Give the following values as per your organization standards 

Data root directory  

User database directory 

User database log directory

Temp DB directory 

Temp DB log directory

Backup directory 

Install the default unless you got explicit request.  

 click next 

click next 

click next 

 click next 

 click next 

Click Close if successful