Change compatibility level

Using below statements you can change comparability level of  all user databases  with a single click. Comparability level is decided bases on sql server product level.

SQL Server	Comparability Level	SQL Version
SQL 2016	130
SQL 2014	120	12
SQL 2012	110	11
SQL 2008	100	10
SQL 2005	90	9
SQL 2000	80	8

use master;

go

DECLARE @SQLVer varchar(10);

-- select the compatibility level based in sql server version

select @SQLVer = CASE  SUBSTRING(convert(varchar,SERVERPROPERTY('productversion')),1,2)

when 12 then '120'

when 11 then '110'

when 10 then '100'

when 9. then '90'

when 8. then '80'

end

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

-- filer user database names from sysdatabases

select name as DatabaseName from sys.sysdatabases where ([dbid] > 4)

OPEN UserDatabases_CTE_Cursor

DECLARE @dbName varchar(100);

DECLARE @compatQuery varchar(500);

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

While (@@FETCH_STATUS <> -1)

BEGIN

-- set database compatibility level

set @compatQuery =  'ALTER DATABASE [' + @dbName + '] SET COMPATIBILITY_LEVEL = ' + @SQLVer

-- Execute compatability script

EXEC (@compatQuery)

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

END

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

GO

xp_cmdshell help; The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Execute below statements to enable "xp_cmdshell" and 

EXEC sp_configure 'xp_cmdshell', 1  

GO  

RECONFIGURE  

GO

After executing above statements if you are facing below error

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

Try below statements to enable advanced options  

EXEC sp_configure 'show advanced options', 1;  

GO  

RECONFIGURE;  

GO  

EXEC sp_configure 'xp_cmdshell', 1;  

GO  

RECONFIGURE;  

GO

Change password in remote session

How to change the password in a Remote Session when you connect the machine through Citrix or VDI 

Goto Remote session 

Press

CTRL + ALT +END 

you can find change password option.  

Click on change Password, 

Provide 

                  user name, current password, New password, confirm password  

And press symbol -->  in confirm password text box 

Get SQL Server Service Account using T-SQL

Identify the SQL Server Service Account using T-SQL 

You can find the service account information in Services 

Go to Start --> Run --> Services.msc 

Right click on SQL Server Services and go to Properties. 

The account information is available under Log On Tab

And also service account information stored in Windows Registry, using xp_instance_regread extended stored procedure we can read windows registry.  Registry entries are like below 

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLServerAgent

Using below script also we can fetch the information   

DECLARE       @DBEngineLogin       VARCHAR(100)

DECLARE       @AgentLogin          VARCHAR(100)

EXECUTE       master.dbo.xp_instance_regread

              @rootkey      = N'KEY_LOCAL_MACHINE',

              @key          = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',

              @value_name   = N'ObjectName',

              @value        = @DBEngineLogin OUTPUT

EXECUTE       master.dbo.xp_instance_regread

              @rootkey      = N'HKEY_LOCAL_MACHINE',

              @key          = N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',

              @value_name   = N'ObjectName',

              @value        = @AgentLogin OUTPUT

SELECT        [DBEngineLogin] = @DBEngineLogin, [AgentLogin] = @AgentLogin

GO

Output 

in above scenario both Agent and SQL Services are running as Local System 

Using DMVS also we can fetch the information   

SELECT

      servicename

      , service_account

      , startup_type_desc

      , status_desc

      , is_clustered

FROM   sys.dm_server_services

Output :

in above scenario SQL Services is running as NT Service and Agent is running with Domain Account. 

Error 1068: The dependency service or group failed to start - "World Wide Web Publishing Service service"

Error : Could not start the World Wide Web Publishing Service service on the local computer.

Error 1068: The dependency service or group failed to start

Resolution : 

Step 1:   Goto Run --> services.msc to open services 

Step 2: Right click on World Wide Web Publishing Services goto properties

Step 3:  goto Dependencies tab and check what are the dependencies. 

Step 4: Check the dependencies, if any service is disabled/stopped. Enable and start the services. 

Step 5:  Once all the dependent services are started. start "World Wide Web Publishing Service" 

Generate Script for all Non-Cluster Indexes in a database

you can't generate script for all Non-cluster indexes (Script for only Non-cluster indexes) by right on database --> tasks --> Generate Scripts. It will generate scripts including table schema. To generate only non-clusterindexes use below script. 

using  below script you can generate script for all Non Cluster indexes in a database 

declare @EmptyString varchar(1)

select @EmptyString = ''

-- 35 is the lenght of the name field of the master.dbo.spt_values table

declare @IgnoreDuplicateKeys varchar(35),

    @Unique varchar(35),

    @IgnoreDuplicateRows varchar(35),

    @Clustered varchar(35),

    @Hypotethical varchar(35),

    @Statistics varchar(35),

    @PrimaryKey varchar(35),

    @UniqueKey varchar(35),

    @AutoCreate varchar(35),

    @StatsNoRecompute varchar(35)

select @IgnoreDuplicateKeys = name from master.dbo.spt_values 

    where type = 'I' and number = 1 --ignore duplicate keys

select @Unique = name from master.dbo.spt_values 

    where type = 'I' and number = 2 --unique

select @IgnoreDuplicateRows = name from master.dbo.spt_values 

    where type = 'I' and number = 4 --ignore duplicate rows

select @Clustered = name from master.dbo.spt_values 

    where type = 'I' and number = 16 --clustered

select @Hypotethical = name from master.dbo.spt_values 

    where type = 'I' and number = 32 --hypotethical

select @Statistics = name from master.dbo.spt_values 

    where type = 'I' and number = 64 --statistics

select @PrimaryKey = name from master.dbo.spt_values 

    where type = 'I' and number = 2048 --primary key

select @UniqueKey = name from master.dbo.spt_values 

    where type = 'I' and number = 4096 --unique key

select @AutoCreate = name from master.dbo.spt_values 

    where type = 'I' and number = 8388608 --auto create

select @StatsNoRecompute = name from master.dbo.spt_values 

    where type = 'I' and number = 16777216 --stats no recompute

select 'CREATE NONCLUSTERED INDEX ['+i.name+'] ON [dbo].['+o.name+'] ('+

  case when index_col(o.name,indid, 1) IS NULL then '' else '[' + index_col(o.name,indid, 1) + '] ASC'end +''+

  case when index_col(o.name,indid, 2)IS NULL then ''  else +',['+index_col(o.name,indid, 2)+ '] ASC'end+''+

  case when index_col(o.name,indid, 3)IS NULL then '' else +',['+index_col(o.name,indid, 3)+ '] ASC'end+''+

  case when index_col(o.name,indid, 4)IS NULL then '' else +',['+index_col(o.name,indid, 4)+ '] ASC'end+''+

  case when index_col(o.name,indid, 5)IS NULL then '' else +',['+index_col(o.name,indid, 5)+ '] ASC'end+''+

  case when index_col(o.name,indid, 6)IS NULL then '' else +',['+index_col(o.name,indid, 6)+ '] ASC'end+''+

  case when index_col(o.name,indid, 7)IS NULL then '' else +',['+index_col(o.name,indid, 7)+ '] ASC'end+''+

  case when index_col(o.name,indid, 8)IS NULL then '' else +',['+index_col(o.name,indid, 8)+ '] ASC'end+''+

  case when index_col(o.name,indid, 9)IS NULL then '' else +',['+index_col(o.name,indid, 9)+ '] ASC'end+''+

  case when index_col(o.name,indid, 10)IS NULL then '' else +',['+index_col(o.name,indid, 10)+ '] ASC'end+''+

  case when index_col(o.name,indid, 11)IS NULL then '' else +',['+index_col(o.name,indid, 11)+ '] ASC'end+''+

  case when index_col(o.name,indid, 12)IS NULL then '' else +',['+index_col(o.name,indid, 12)+ '] ASC'end+''+

  case when index_col(o.name,indid, 13)IS NULL then '' else +',['+index_col(o.name,indid, 13)+ '] ASC'end+''+

  case when index_col(o.name,indid, 14)IS NULL then '' else +',['+index_col(o.name,indid, 14)+ '] ASC'end+''+

  case when index_col(o.name,indid, 15)IS NULL then '' else +',['+index_col(o.name,indid, 15)+ '] ASC'end+''+

  case when index_col(o.name,indid, 16)IS NULL then '' else +',['+index_col(o.name,indid, 16)+ '] ASC'end+''+') ON [Primary]'  

from sysindexes i, sysobjects o

where i.id = o.id and

      indid >1 and indid < 255 --all the clustered (=1), non clusterd (>1 and <251), and text or image (=255) 

      and o.type = 'U' --user table

      --ignore the indexes for the autostat

      and (i.status & 64) = 0 --index with duplicates

      and (i.status & 8388608) = 0 --auto created index

      and (i.status & 16777216)= 0 --stats no recompute

      order by o.name

Installing and configuring SQL Server 2012 Performance Dashboard Reports

Installing and configuring SQL Server 2012 Performance Dashboard Reports

The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.

The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.

Common performance problems that the dashboard reports may help to resolve include:

1. Ø  CPU bottlenecks (and what queries are consuming the most CPU)
2. Ø  IO bottlenecks (and what queries are performing the most IO)
3. Ø  Index recommendations generated by the query optimizer (missing indexes)
4. Ø  Blocking
5. Ø  Latch contention

To install this add-on please go through below link

http://www.microsoft.com/en-us/download/details.aspx?id=29063

Download & copy SQLServer2012_PerformanceDashboard.msi file into server where you want to install.

 

Double click on downloaded file to install SQL Server 2012 performance dashboard reports

 

Clink on run

Click on Next

 

Select I accept the terms in the license agreement  

And click on next

Enter your name and company. Click on Next button.

If you want change the default installation location click on Browse and provide appropriate location

Click on Next

Dash board report will install files in above location

Click on Install button

Click on finish button

To load reports – right click on server name and expand reports and click on Custom Reports

Select the .rdl file from below location shown as in below images

 

Click on Run button

It will give above error. You need to execute the setup.sql files located in installation directory.

Open the file in Query Analyser

 

Execute the script

 

After successful execution  please load the report by selecting .rdl file. It will show the report as below

 

 

NetFx3 error while installing SQL Server 2012 on windows 2012 server

Problem description

TITLE: Microsoft SQL Server 2012 Service Pack 1 Setup

The following error has occurred:

Error while enabling Windows feature : NetFx3, Error Code : -2146498298 , Please try enabling Windows feature : NetFx3 from Windows management tools and then run setup again. For more information on how to enable Windows features , see http://go.microsoft.com/fwlink/?linkid=227143

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.3000.0&EvtType=0x681D636F%25401428%25401

- 

Solution 

Goto server manager

 --> Click on manager right side corner of window

--> Click Add Roles and Features

Blow Add Roles and features wizard window appears 

click on next

in the next screen click on Role-based or feature-based installation

and click on the next

in the Server Selection Screen select the first radio button i.e. Select a Server from the Server Pool 

In the server pool window you can see the server name 

On Server Roles page simply click on next

in Features page wizard clicked on the check box next to ".NET Framework 3.5 Features", and then clicked on the Next as shown in below image

and in final wizard click on install to add features

and installation progress as below 

Thanks & Regards
Venkat Sangu

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