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 

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 :  

<![if !supportLists]>1.       <![endif]>Express : 512 MB

<![if !supportLists]>2.       <![endif]>Other  : 1 GB

       Recommended

<![if !supportLists]>                                                         i.            <![endif]>Express : 1 GB

<![if !supportLists]>                                                       ii.            <![endif]>Other  : 4 GB

Installation Steps:

Go to setup file location  click on setup.exe

Next you will see the SQL Server Installation Center  page. In Left side pane select Installation Tab

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

                               

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

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

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

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

SQL Server Job history

Using below script you can fetch the specific job history like executing date and durations

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

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

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

Error messages when restoring from different versions of SQL Server.

We can't restore the backups taken in Newer version of SQL Server instance on older version of SQL Server. 

SQL Server 2008 R2 to SQL Server 2008

Msg 3169, Level 16, State 1, Line 1

The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

SQL Server 2008 R2 to SQL Server 2005

Server: Msg 3241, Level 16, State 7, Line 1

The media family on device 'f:\temp\test001_sql2008r2.bak' is incorrectly formed. SQL Server cannot process this media family.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

SQL Server 2008 R2 to SQL Server 2000

Server: Msg 3169, Level 16, State 1, Line 1

The backed-up database has on-disk structure version 661. The server supports version 539 and cannot restore or upgrade this database.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

SQL Server 2008 to SQL Server 2005

Server: Msg 3241, Level 16, State 7, Line 1

The media family on device 'f:\temp\test001_sql2008.bak' is incorrectly formed. SQL Server cannot process this media family.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

SQL Server 2008 to SQL Server 2000

Server: Msg 3169, Level 16, State 1, Line 1

The backed-up database has on-disk structure version 655. The server supports version 539 and cannot restore or upgrade this database.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

SQL Server 2005 to SQL Server 2000

Server: Msg 3169, Level 16, State 1, Line 1

The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

2015-02-17 12:27:38.19 spid15s     Error: 26014, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     Unable to load user-specified certificate [Cert Hash(sha1) "5B4FF9FFF4E6752A3AD51489E1A9C455E580BCC3"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.

2015-02-17 12:27:38.19 spid15s     Error: 17182, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property. 

2015-02-17 12:27:38.19 spid15s     Error: 17182, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property. 

2015-02-17 12:27:38.19 spid15s     Error: 17826, Severity: 18, State: 3.

2015-02-17 12:27:38.19 spid15s     Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2015-02-17 12:27:38.19 spid15s     Error: 17120, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Event ID 17182: TDSSNIClient initialization failed with error 0x7e, status code 0x3a.

Event ID 17826: Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

Event ID 17120: SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Solution :

1. Check 'Forced Encryption' (Turned False) and 'Certificate' value is cleared under SQL Server Configuration Manager.
2> Check the following registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\SuperSocketNetLib\Certificate
and clear the Certificate values.

Script to get the SQL Logins information

Login type descriptions

S = SQL Server user(SQL_USER)

U = Windows user(WINDOWS_USER)

G = Windows group(WINDOWS_GROUP)

A = Application role(APPLICATION_ROLE)

R = Database role(DATABASE_ROLE)

C = Certificate mapped(CERTIFICATE_MAPPED_USER)

K = Asymmetric key mapped(ASSYMETRIC_KEY_MAPPED_USER)

Using below script you can list all Login Accounts in a SQL Server

SELECT name AS Login_Name, type_desc AS Account_Type

FROM sys.server_principals 

WHERE TYPE IN ('U', 'S', 'G')

ORDER BY name, type_desc

Using below script you can list all  SQL Login Accounts (Not windows logins)  only in a SQL Server 

SELECT name

FROM sys.server_principals 

WHERE TYPE = 'S'

ORDER BY name, type_desc

Using below script you can list all Windows Login Accounts (Not SQL Logins)  only in a SQL Server 

SELECT name

FROM sys.server_principals 

WHERE TYPE = 'U'

Using below script you can list all Windows Group Login  (Not SQL Logins)  only in a SQL Server 

SELECT name

FROM sys.server_principals 

WHERE TYPE = 'G'

PDW Database Restore

Using below command you find the backup header information for all backup sets on a given backup devise

Restore headeronly from disk = '\\10.10.10.10\PDWBackups\FullBackup_02052015\DWLOADER_staging'

Restore a database using Full backup 

Restore database DBAAdmin from disk = '\\10.10.10.10\PDWBackups\FULL\05052015\AdventureWorksPDW2012.bak'

Restore a database using differential backup 

Restore Database DBAAdmin1 from disk = '\\10.10.10.10\PDWBackups\FULL\05052015\AdventureWorksPDW2012_Diff.bak' 

with BASE = '\\10.10.10.10\PDWBackups\FULL\05052015\AdventureWorksPDW2012.bak'  

 

In above statement full backup is '\\10.10.10.10\PDWBackups\FULL\05052015\AdventureWorksPDW2012.bak'  

and differential backup is \\10.10.10.10\PDWBackups\FULL\05052015\AdventureWorksPDW2012_Diff.bak' 

Limitations in the restoration is 

The backup set can only be restored to a SQL Server PDW destination appliance that has the same number or more Compute nodes than the source appliance. It cannot have fewer Compute nodes than the source appliance.

SEE ALSO 
How to Take PDW DB Backup
How to connect PDW Instance
Grant access on PDW Backup Share/Node

PDW Database Backup

The backup is stored in the G:\Backups directory on the appliance 

There are two type of backup 

Full Backup : A full database backup is a backup of an entire  PDW database similar to SQL Server Backup.

Differential Backup :A differential database backup only includes changes made since the last full PDW Database backup 

Syntax :  FULL BACKUP 

use master

backup database AdventureWorksPDW2012 to disk = '\\10.10.10.10\PDWBackups\FULL\05052015\AdventureWorksPDW2012.bak' 

The above statement creates the full backup of of the AdventureWorksPDW2012 database . The backup will be stored in \\10.10.10.10\PDWBackups\FULL\05052015\ location. To grant access on this shared location please use sp_pdw_add_network_credentials  command 

Syntax :  DIFFERENTIAL BACKUP 

backup database AdventureWorksPDW2012 to disk = '\\10.10.10.10\PDWBackups\DIFFERENTIAL\05052015\AdventureWorksPDW2012.bak with differential' 

 

You can not perform differential backup on master database

You can copy a database to a different appliance by copying the backup files to the G:\Backups directory on the Backup node of the target appliance and then performing a restore. However, copying backup files to a different location can negatively impact restore performance. This can occur when the logical disk storage for the backup files varies from the original backup directory.
The target appliance for restoring the backup must have at least as many Compute nodes as the source appliance. The target can have more Compute nodes than the source appliance, but cannot have fewer Compute nodes.

Execute queries in PDW

After login into PDW Server ( How to Login into PDW )

 Right click on the server name and click on New Query option in the pop-up window

It will open new window in right side 

Type the query to execute in right side window 

to execute query use below option 

 Mark the query which you want to execute and use CTRL + SHIFT+ E

or Mark the query which you want to execute and click on the below button

it use master database and execute the query and shown below results 

use below options to show the results in grid,text, file

How to connect PDW

You can login pdw using MS Visual Studio 2012/SQL Data Tools  or Nexus Query Chameleon

Lets try using Visual Studio 2012 

Goto Visual Studio 2012 icon and click 

Click on Run the program without getting help

you can see 

Click on View and select SQL Server Object Explorer 

now you can see below screen right click on the SQL Server 

Select Add SQL Server 

Provide the PDW server and Login & password details ( you can login using SQL Authentication or using Windows Authentication) 

and click on connect 

How to grant access on backup shared in PDW

How to grant access on backup shared in PDW

To backup the database we have to assign access permissions to a windows account that has write access to a file share on a target server.

sp_pdw_add_network_credentials '10.10.10.10','corp\adAcctLogin', 'PassWord'

To remove access to shared path  

sp_pdw_add_network_credentials '10.10.10.10','corp\adAcctLogin', 'PassWord'

To check who are all having permissions 

select * from sys.dm_pdw_network_credentials

What is Trace Flag? Enable / Disabling Trace Flag

Trace flag is a directive used to " set specific server characteristics or switch off a perticular behavious" -- is a setting that in some way or another alter the behaviour of various SQL Server functions

Trace flags are used to generate internal information regarding SQL Server's activities, primarily so that Microsoft developers can troubleshoot their code.

Trace flags are enbled in SESSION or GLOBAL levels

How to check whcih trace flags are enabled in the server globally

DBCC  TRACESTATUS(-1) 

Above command will display the status of all trace flags that are currently enabled globally.

How to check whcih trace flags are enabled in the server for current session

DBCC  TRACESTATUS()

Above command will display the list of all trace flags that are enabled for the current session 

You can enable/implement Trace Flags in several ways

• using SQL Server Configuration Manager
• using Registry Editor
• using Query Analyser

Implementing Trace Flag using SQL Configuration Manager 

This is also know as Enabling Trace Flag using –T Startup Option

Adding trace flag in Startup Parameters 

-T startup option to specify that the trace flag be set on during startup. Saparate each trace flag by semi-colon(;). -T startup option enables a trace flag globally 

You can't enable a session level trace flag  by using start up option

  

Implementing Trace Flags using Registry (REGEDIT.MSC)

This is not a recomended method. please avoid using it in production box.

• Goto Run and type regedit.msc and press endter
• Goto below location in registry
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\Parameters
• Add the trace flag by right click on the Parameters folder and click on New and then String Value
• Provide the name as SQLArg3 and then right click on the key value to modify the value.
• Now provide the value as -T1222 

Implementing Trace Flags using Query Analyser 

Using DBCC TRACEON command you can enable the trace flags.All trace flags can't be enabled using DBCC TRACEON e.g. DBCC TRACEON (835), this can be enabled during the server startup only.

You can enable Session Scope or Global Scope using this command

Session Scope : Using below command we can enable session level trace flag

DBCC TRACEON(1222)

Global Scope :  Using below command we can enable global level trace flag

DBCC TRACEON(1222,-1)

Disabling Trace flag

Remove from start-up parameters 

• Go to SQL Server configuration manager and to sql server properties 
• Go to Advanced Tab and click on drop box of start-up parameter and remove the parameter -T1222
• Restart SQL Server services 

Remove from Registry 

• Go to regedit.exe and 
• Browse HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\Parameters
• Remove the string value which has -T1222
• Restart the SQL Server services 

Remove using Query Analyser 

use below command to turn off session level trace flag

DBCC TRACEOFF(1222)

use below command to turn off global level trace flag

DBCC TRACEOFF(1222,-1)

Caution : 

Do not use trace flags in production environment without testing it on non production environments, trace flags should be used under the guidance of Microsoft SQL Server support.