Month: March 2013

Dynamic Pivot Example

Below is the reference code on how to create Dynamic Pivot query :

create table Cust (custname varchar(20),invoiceno varchar(20),amount int)
Insert into Cust
Values ('aa','inv1',100)
,('aa','inv2',50)
,('bb','inv3',200)

Create table TESTCU (custname varchar(20),invoiceno varchar(20),itemno int,Accountno varchar(20))

Insert into TESTCU
VALUES ('aa','inv1',1,'act1'),
('aa','inv1',2,'act2'),
('aa','inv1',3,'act3'),
('aa','inv2',1,'act2'),
('bb','inv3',1,'act1'),
('bb','inv3',2,'act3')

declare @List varchar(200)

select @List =
STUFF((Select distinct ','+quotename(Accountno)
from TESTCU
FOR XML PATH('')),1,1,'')

declare @SQL nvarchar(2000)
set @SQL ='Select B.*,A.amount from CUST A INNER JOIN
(select * from (select custname,invoiceno,Accountno,1 as TR from TESTCU) PT PIVOT (MIN(TR) FOR ACCOUNTNO in (' +@LIST+')) as PS) B
ON B.invoiceno=A.invoiceno'

Execute SP_Executesql @SQL

Advertisement

Running SQL Server on a Static Port

Configure SQL Server to listen on a Static Port:

Go to Configuration manager–> SQL Server Configuration Manager–> Protocols for ‘Instance-name’; –> Right click on TCP/IP Properties.On the IP addresses tab, on the bottom,In the ‘IPAll’ section : blank out the TCP Dynamic Ports and enter a port number in TCP Port option. Restart the SQL Server service.
Configuration
We can look into the sql server error log to validate on which port SQL Server is listening.
If the SQL Browser service is running, no need to mention the port number in the connection string.
If the SQL Browser service is not running, we can either connect as ServerName/InstanceName,PortNumber or create an alias on each client.

To Create an alias –> On each Client, Go to configuration manager–>SQL Native Client Configuration –> Aliases.
Enter an Alias Name and port number on which the connecting server is listening and the connecting server Name.This should be done on every client machine that needs to connect to the server. To connect to the server,enter the Alias name in the SSMS, instead of ServerName\InstanceName.

Sometimes, even though we stopped running the SQL browser service ,we can still connect to the server(other protocols are disabled as well.).The reason here is SQL server is still listening on the same port like it was before(when the browser was running).That’s why we can still connect to the server.

If SQL Server changes the dynamic port number, we will not be able to connect to the server.
One thing I learned is that even though we restart the SQL server,it will try to use the same port number even when dynamic ports are enabled unless it was taken by some other service.Only when the port number was taken by some other service, it will use a new port.

Example: Let’s say,we enabled dynamic ports. SQL Server is running on port 61023. SQL Server Browser is running.We can connect to the SQL Server just fine.Now,Stop the SQL Server Browser. Restart the SQL server. Connect to the SQL server.If you can still connect to the server, it means that the SQL server is listening on the same port as before(when SQL browser was running).

Dedicated Administrator Connection

Dedicated Admin Connection(DAC) feature is available in sql 2005 and later. It let’s the administrator connect to the sql server and is very handy, if things are going crazy with the server and nobody could connect to the sql instance. DAC listens on a different port than SQL Server. So, the firewall should not block the DAC port. You can know on which port DAC is listening by looking into the SQL Error Log.

We can also use DAC for other purposes, such restore master database etc. To use DAC, we need to first enable DAC using sp_configure.

sp_configure 'remote Admin Connection',1
GO
Reconfigure

So, to use DAC, the server has to be in single user mode. To do this, go to configuration manager and in the properties on the start up parameters, add -m to it and restart the sql server service. Do not start the sql server agent and Full Text Services. Now, in the Management Studio, do not click on the Connect in the Object Explorer. Click on the New query and

Enter Admin:ServerName\InstanceName and hit Connect.(make sure your windows authentication has sysadmin privileges on the server.) This should get you through. Sometimes, you will get an error saying,
Server is in single user mode and only one administrator is allowed to connect to the server .
Double check to make sure sql server agent and any other service(s) are not running and additionally, you can also go through SQL ERROR Log to see which login is connected. If you are using express edition, you have to set trace flag 7806 to use DAC.

The keyword ‘Admin:’ is to tell SQL server to make the connection as DAC Connection. If sql browser service is not running and If we try to make DAC connection as Admin:ServerName\InstanceName, it will not work.In such case, check the error log to find on which port DAC is runnin and call it as ServerName\InstanceName,DACPortNumber(do not add “Admin”).

If SQL browser is running, you can simply call it as Admin:SqlserverName\InstanceName

Enabling Instant File Intialization

When you create a new database, restore from backup or when a database has to grow, it zeroes out the content on the disk before writing on to the disk. This can affect the performance and may lead to query time outs.

The reason it zeroes out the content is because of potential security concern. With IFI enabled, we can let SQL server start writing out to the disk without zeroing out the disk content. This applies only for data files. Log files are always zeroed initialized.

To enable Instant File Initialization, SQL Server Service account should have permissions to do perform volume maintenance tasks.

To do this—> go to Local Security Policy–>Local Policies–>User Rights Assignment–>Perform Volume Maintenance Tasks and right click on it and add the sql server service account to it.

If the sql server is running as a member of the administrator group, this option is enabled by default. If the sql server is running, when you enable IFI, it has to be restarted to take into effect. Also, In a clustered environment all the nodes should have this feature enabled. To check this feature, try the sample script below after and below assigning the permissions.(If the sql server service is running with admin rights, it is already enabled).

DBCC TRACEON(3004,3605,-1)

–>Create a Sample database

–>Check the sql server error log. If IFI is not enabled, you will see zeroing out the mdf and you will not see that after it is enabled.

DBCC TRACEOFF(3004,3605,-1)

Some of the reasons why IFI does not work:

1.Database has TDE enabled and

2.Trace Flag 1806 is turned on.
Reference : http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx

STUFF AND FOR XML PATH for String Concatenation

We can use XmlPath('') to concatenate column data into single row. Stuff is used to remove the first ‘,’ after string concatenation.

declare @Test Table(sno int,scity varchar(20))
Insert @Test(sno,scity)
Values
(1,'Chicago'),(1,'Detriot')
,(2,'Memphis'),(2,'Nashville')
,(3,'New York'),(3,'Dallas')
,(4,'Atlanta'),(4,'Houston')
Capture12
select distinct sno ,
STUFF((Select ','+Scity
from @Test T1
where T1.sno=T2.sno
FOR XML PATH('')),1,1,'') from @Test T2

Capture13

Log Shipping Role Reversal Process

Log Shipping :

Log Shipping is one of the four techniques we have for High availability(with each one having its own Pros and Cons).

Out of all the options, IMHO, Log Shipping is easier to configure and monitor because it is based on Simply Backups and Restore concept.

In a nutshell, you basically will take Log backup(s) on the Primary database and copy them to the destination server where the secondary server can access and apply them on the secondary database.

We can configure more than one Secondary database using Log Shipping. This cannot be done with database mirroring.
Another major score is both the servers do not need to communicate directly. (I.e. the secondary server could really be a remote server).
Log shipping needs database to be either in full or bulk recovery mode.
You can also restore the logs in standby mode which gives the users the ability to access the secondary database.
In this post, I would like to discuss on the steps to be taken in order to reverse the roles:

1. Disable the Log Shipping Jobs on the Primary and Secondary Server.
2. Make sure all the log backups taken on the primary were copied on to the Network Folder and restored on to the Secondary. If not, apply them.
3. Backup the Tail log on the primary with no recovery. This will set the primary database in ‘restoring mode’.
4. Apply the tail log backup on the primary with recovery. This brings the secondary database online.
—To reverse the role—–
5. Configure the New Secondary database as Primary Log Shipping Database just like Initial Log shipping configuration.
6. Instead of the creating the secondary database from scratch, select ‘No. The secondary database is initialized’ while creating the secondary database and choose the secondary database from the drop down.
7. Choose the restoring as per your need either restoring or stand by. I prefer standby mode as users can connect to it but it purely depends on the purpose of doing Log Shipping , i.e. is it just for high availability or for both(HA + Offload the query distribution like reporting).

Restore Master Database

Sometimes, you need to restore the master database. Restoring master db is different than other user databases. I followed the below instructions while doing the restore .
1.Set the server in single user mode. This can be done by adding -m to the startup parameters in the configuration manager(right click on the sql service and go to properties) and restart the sql server.
2.Disable the sql server agent.
3.Go to command prompt and connect to the sql server as SQLCMD -E.If your windows account has sysadmin rights on the server, you can get through it else, specify the credentials. Also, if it is not the default instance, you will need to mention the servername\InstanceName.
Example : SQLCMD -S SERVERNAME\INSTANCE -U LOGINNAME -P MYPASSWORD
4.After connecting to the server, you can run the below
RESTORE DATABASE MASTER FROM DISK = ‘E:\BACKUPPATH\BACKUPFILE.BAK with REPLACE’
This will replace the master database with your backup copy. There are couple of other ways to do but I find this one easy to deal in these scenarios. As a tip one other option is using DAC(Dedicated Admin Connection).

Basic Info on all the databases on the Instance

The below query returns some basic and must known information about all the databases on the instance. It works on versions 2005 and above.
select name,A.Dbsize_MB,
case when databasepropertyex(name,'isautoshrink') =1 then 'Auto Shrink' else 'No Auto Shrink' end as [AutoShrink],
case when databasepropertyex(name,'IsTornPageDetectionEnabled') =1 then 'Page Verify' Else 'No Page Verify' end as [TornPageDetection],
case when databasepropertyex(name,'IsAutoClose') =1 then 'Auto Close' Else 'No Auto Close' end as [AutoClose],
databasepropertyex(name,'status') as [DBStatus],
databasepropertyex(name,'recovery') as [RecoveryMode],
databasepropertyex(name,'UserAccess')as [User_Acess],
databasepropertyex(name,'Updateability') as [Updatebility],
databasepropertyex(name,'IsAutoCreateStatistics') as [AutoCreateStatistics],
databasepropertyex(name,'IsAutoUpdateStatistics') as [AutoUpdateStatistics],
databasepropertyex(name,'isPublished') as [Replication_Publisher],
databasepropertyex(name,'isPublished') as [Replication_Subscriber]
from master.dbo.sysdatabases INNER JOIN
(select (cast(sum(size) as float))*8/1024 as [DBsize_MB],db_name(database_id) as DbName
from sys.master_files group by database_id) A on A.DbName=Name
where name not in ('TempDB')
order by A.DbSize_MB