Change VLANS/ IP Addresses of a SQL Server failover cluster instance

 

This has been performed in SQLServer 2012 SP3, A-P cluster setup on windows server 2012. These are virtual machines build on Hypervhosts. Requires a sysadmin/network admin for updating DNS or adding isci names to SAN

In Active node:

  1. Go to Failover cluster manager–>roles–>select SQL Server service name–>in the bottom choose resources tab—>select server name—>right click take offline
  2. Go to network and sharing center—>change network adapter settings–>select your adapter–>right click properties–>in networking tab select Internet protocol version 4       –>properties and make changes to the IP Address, subnet mask, gateway and preferred DNS (according to your requirement) –> you will lose connection
  3. Repeat the same thing for any additional adapters with a new IP Address as subnet mask, gateway and preferred DNS could remain same mostly
  4. Login into the server
  5. Connect to iSCSI initiator and check if LUNs are connected still
  6. Add iSCSI names to SAN
  7. If LUNs are not connected, then reconnect them and chose enable multipath
  8. Go to Failover cluster manager–>roles–>select SQL Server service name–>in the bottom choose resources tab–>select server name–>properties, General tab–>in IP Addresses–>add new IP Address the new network will show up automatically as you have updated the network adapter settings
  9. Go to a command prompt by running as admin and run ipconfig/registerDNS to register your DNS
  10. Update VM VLAN on HyperVHost
  11. Update SQL DNS cluster IP for that cluster in DNS
  12. Select the windows cluster name–>in the right side in cluster core resources–>select server name–>right click properties–>in the IP Address add new IP Address

In Passive node:

  1. Go to network and sharing center–>change network adapter settings–>select your adapter–>right click properties–>in networking tab select Internet protocol version 4–>properties and make changes to the IP Address, subnet mask, gateway and preferred DNS (according to your requirement) –> you will lose connection
  2. Repeat the same thing for any additional adapters with a new IP Address as subnet mask, gateway and preferred DNS could remain same mostly
  3. Go to a command prompt by running as admin and run ipconfig/registerDNS to register your DNS
  4. Remove old IP Addresses from cluster core resources as well as server name properties (wherever you added the new IP Addresses)

In active node:

  1. Remove old IP Addresses from cluster core resources as well as server name properties (wherever you added the new IP Addresses)
  2. Perform failover and see if all is well

On your PC:

  1. From command prompt do ipconfig/flushDNS

 

Hope this helps!

 

Posted in SQL Server DBA Stuff | Tagged , , , | Leave a comment

TSQL to output result set in an email in a tabular format without showing number of rows affected

Hello,

I was thinking of configuring an alert for our application for which the output is a resultset of a query displayed in an email. The alert was good but since it was a direct output of a query it was not properly formatted. I wanted the output in a tabularized result set format without showing the no of rows affected. And here it is shown below.

SET NOCOUNT ON

Declare @cnt int
declare @alertSubject varchar(100) = 'Subject here';
declare @msg varchar(MAX)
select @cnt=count(1) from Table where condition='this or that'

set @msg =
'
<table border=1>' +
'
<tr>
<th>Col1</th>
<th>Col2</th>
<th>Col3</th>
<th>Col..n</th>
</tr>
' +
CAST ( (
select td = cast(Col1 as varchar(40)),'',
td = cast(Col2 as varchar(40)),'',
td = cast(Col3 as varchar(40)),'',
td = cast(Col..n as varchar(40))
from Table where condition='this or that'
FOR XML PATH('tr'), ELEMENTS XSINIL, TYPE
) AS varchar(MAX) ) +
'</table>
'

if @cnt >=1
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'DBAGroup',
@recipients = 'DBAs@xyz.com',
@query_result_separator= ' ',
@query_result_no_padding= 1,
@body = @msg,
@body_format = 'HTML',
@importance = 'High',
@subject = @alertSubject;
end</pre>
<pre>

Hope this helps!

Posted in SQL Server DBA Stuff | Tagged , , , , | 1 Comment

TSQL to generate JSON insert script for a collection with name same as sql server table

Hello,

I was trying to migrate a SQLServer database to a MongoDB database with SSIS and to do this the SSIS transform expects an empty row in each collection in mongodb. I found this challenging because I had 50+ tables in the SQLServer database and I was supposed to insert a null row per collection. I played around with TSQL a little bit and came up with the below script. The script below generates an insert statement with empty values per table in SQLServer database.

with cte as(
select object_name(object_id) Table_Collection_Name,name from sys.columns where object_id in ( select id from sys.sysobjects where xtype='U'))
select Table_Collection_Name,InsertStatement from ( 
SELECT     
         Table_Collection_Name,
            'db.'+Table_Collection_Name+'.insert([{' + STUFF((    SELECT ',' + SUB.name +':null'
                        -- Add a comma (,) before each value
                        FROM cte SUB
                        WHERE
                        SUB.Table_Collection_Name = CAT.Table_Collection_Name
                        FOR XML PATH('') 
                        ),1,1,'') +'}])'
AS [InsertStatement] 
FROM  cte CAT
) x
group by 
Table_Collection_Name, InsertStatement
order by Table_Collection_Name, InsertStatement

With SQLServer 2016 you should be able to generate the JSON script from SSMS itself using ‘for Json’ option. You can find more information on this here

Hope this helps!

Posted in SQL Server DBA Stuff | Tagged , , , , , , , | Leave a comment

SQL Server 2012 SE Cluster: SAN Upgrade Steps

Hello every one. Hope all is well. Its been a while since my last post. Today I am going to write about SAN upgrade for one of my test sqlserver cluster instances. I am using SQL Server 2012 SE Active passive cluster and just upgraded the SAN from SAS to an SSD SAN.  The upgrade involved 5 phases.

Phase 1: Capture the storage requirements and create storage

  • Carve the LUNS on the new SAN and present them to the database server. I sent the storage requirements to our SAN Admin and he created the LUNS and gave me the discovery portal IP Address.

Phase 2: Present the new storage to the database server

  • To make the new LUNS available to the database server launch the iSCSI initiator discovery tab in the database server. In the discovery tab click on Discover portal and enter the discovery portal IP. Now go to Targets tab and click refresh and you should be able to see all the new LUNS with status inactive. Select the appropriate target and hit connect until you connect all your drives. When you click connect enable Multi path IO and ensure you checked the ‘add to favorites’ check box. At this time the server should be able to see all the drives.
  • Go to ‘Create and format hard disk partitions’ or Disk Management
  • In the bottom portion the new disks should appear. Initialize the disk and assign the drive letters and format the disk.
  • Do this on Active node and Passive node of the cluster
  • On the active node get the disks online

Phase 3: Present the storage to the cluster and SQL Server

  • Go to the Failover cluster manager –> ClusteràStorage–> disks in the Actions on the right side click on Add Disk and add the new disks individually
  • The cluster is now able to see the new disks
  • To make the disks available to the SQL Server Instance go to Failover Cluster Manager–>Cluster–>Roles–>MSSQLServer–> right click select Add Storage
  • Right click MSSQLServer in Roles and check the show dependency report and the new disks are still not associated with the actual instance and freely lying
  • To make them available to the instance go to Roles–>MSSQLServer in the bottom select the resources tab and go the other resources right click on SQLServer–>properties–> dependencies tab and add the new cluster disks as shown below:1
  • Now the new drives are available to the SQLServer instance.

Phase 4: Backup and Move the databases

  • Backup all the databases (user and system databases)
  • Script out all the logins and their appropriate permissions and save it (not required but just safer side)
  • Stop if there is any replication setup and disable and stop publications. If you want you can also delete the distribution database if it is not already deleted after disabling the replication
  • Script out and save sql server agent jobs
  • I ran the following commands to move the Tempdb from T:\ to U:\
USE master;

GO

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'U:\Data\tempdb.mdf');

GO

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'U:\Log\templog.ldf');

  • To move the quorum drive go to Failover Cluster Manager –> Cluster right click–>More actions–> Configure Cluster Quorum Settings:

 

2

 

3

4

5

Selected the new quorum drive

6

7

Click on Finish. This would not require SQLServer service or cluster service down.

  • Took all the app services down and Detached all the user databases and then took the sql server services down
  • Manually copied the Tempdb files to the new drive which is U:
  • I gave the root folder as D:\ at the time of installing the cluster. Hence the Master, Model and MSDB databases were created in

D:\MSSQL11.MSSQLSERVER\MSSQL\DATA  and

D:\MSSQL11.MSSQLSERVER\MSSQL\LOG

  • I copied the entire folder MSSQL11.MSSQLSERVER from D:\ to the appropriate new drive
  • Changed Drive letter of D:\ to unused letter and then changed the drive letter of the new drive to D:\
  • Brought the sql server services online
  • Changed the Old data, log, Tempdb data, Tempdb log letters to an unused letter
  • Changed to new data, new log drive letters to the previous ones
  • Attached all user databases
  • I was also using filestream in one of my databases. I restored that database instead of detaching/attaching
  • I wanted the Tempdb drive letter as T:\ so ran the above commands again with T:\ since the old tempdb drive letter was changed to unused drive letter
  • Took sql server services down, changed drive letter U:\ to T:\
  • Started the sql server services
  • Checked if all database are accessible and if I am able to run my queries and see the correct results
  • Started the app services back

Phase 5: Remove the Old storage

  • Go to failover cluster manageràcluster–>Storage–>Disks
  • Select appropriate disk right click and select Take offline
  • Once all the drives are offline go to iSCSI intiator and in the Targets tab select the appropriate target and click on Disconnect
  • Once this is done go to Favorite targets and remove them from there as well.

 

 

That was the final step. This is a very small environment and it took me around 10 mins to copy around 60GB of data and log files and a total of 30 mins to complete the upgrade. Hope this helps.

Posted in SQL Server DBA Stuff | Tagged , , , , , , , , | 1 Comment

T-SQL to send email

Hello,

Below piece of t-sql will help you send email. I use this in some test jobs whenever I don’t want to send unwanted emails to the entire DBA team. An instance of this is when I am testing some purge jobs I would only check notify when job fails in the SQLServer agent job notification of that specific job. But the prior step would be to add this piece and route it when any step of the job fails. So the @subject would be ‘Hey your job failed’ and the @recipient would just be me. This step will however succeed and none of the DBA team would get these unwanted emails even though I am using the common profile.


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Enter the profile here eg:DBGroup',
@subject = 'Enter the subject here',
@body = 'Enter your body here',
@recipients = 'Enter your email here',
@body_format = 'text'

Hope this helps.

Posted in SQL Server DBA Stuff | Tagged , , , , | Leave a comment

Function to get Business Hours

Hello,

Its been a while since my last blog post. Today I am blogging about a function that I had to work on to calculate business hours. Per my requirement the start time is 8:30AM and end time is 17:30PM and no business holidays but weekends are off.

Here is the function:


Create FUNCTION [dbo].[GetBusinessHours]
(
@BegDate DATETIME,
@EndDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @NumOfHours INT;
WITH n (n) AS (
SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
), DateTimes (dt) AS (
SELECT TOP (DATEDIFF(hh, @BegDate, @EndDate))
DATEADD(hh, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), @BegDate)
FROM n n1, n n2, n n3, n n4, n n5, n n6
)
SELECT
@NumOfHours = COUNT(1)
FROM
DateTimes dt
WHERE 1 = 1
AND DATEPART(dw, dt.dt) NOT IN (1,7)
AND CAST(dt.dt AS TIME) BETWEEN '08:30:00' AND '17:30:00'
RETURN @NumOfHours
END

GO

Hope it helps.

Posted in SQL Server DBA Stuff | Tagged , , , , , | Leave a comment

Tsql script to select all columns from a table grouped by max column value

Hello,

In this blog I would like to post the t-sql statement to select all columns from a table grouped by max value of a column. This is possible by using the row_number function.

Below is the sample data for which I wrote the script:

rownnumber

My output should be only the highlighted columns from the above image. Below is the script that I wrote to select the accountid, endingdate and contractid with the most recent endingdate (or max endingdate)


with mycte as

(
SELECT ACCOUNTID,ENDINGDATE,CONTRACTID,row_number() Over(partition by ACCOUNTID Order by ENDINGDATE DESC )rn FROM TABLE1

)

SELECT ACCOUNTID, ENDINGDATE, CONTRACTID from mycte Where rn=1 order by ENDINGDATE desc

As specified in the script I am using the row_number() function and partitioning on the accountid since the accountid is the column for uniqueness and ordering by the endingdate as endingdate is the column for which I want to get the maximum value. The select statement in the first part of the script returns the following output:

rownumber1

If you observe the output above the rownumbers are assigned to the accountid’s with 1 for the max endingdate column for each accountid. So the second select statement selects only those endingdate columns with rows whose rownumbers are 1. Here is the final output:

rownumber outptut

 

However if you want to select records with a column with least value then you need to remove the DESC (descending) after the order by in the first select statement of the script.

Hope this helps!

Posted in SQL Server DBA Stuff | Tagged , , , , | Leave a comment