How to deploy SSIS package to SQL Server 2012 SSISDB Catalog

In this post, I’m going to present how to deploy SSIS package to SQL Server 2012 integration services catalog.

  • Create SSIS catalog

1.png

2.png

  • After step 1 you can see SSIS catalog name called “SSISDB”  created. Right click and create a folder “TestSSISPackages”

4.png

 

5.png

 

6

  • Now create a visual studio SSIS integration sample package. For the purpose of this demo I have created a sample package called “TestSSIS”.

7

  • After the step 3, right click on project name and click deploy

8.png

  • The below screen will appear for deploying the package, follow the screenshots to complete the package deployment

9.png

 

10.png

 

11.png

 

12.png

 

13.png

 

14

 

15.png

  • After the step 5, you can see that the package “TestSSIS” deployed in SSIS catalog as shown below.  There you go!

16.png

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

Database size report for multiple database on multiple servers

As a DBA, you are responsible for monitoring the growth of the databases on multiple servers in different environments. There are many ways we can achieve this, my requirement was to use linked servers to get the databases size of all the linked servers and email the report to DBA group.

Here is the script i have written to get the database sizes of all the linked servers.

 

 CREATE PROCEDURE [dbo].[SumofDBsize]
AS
BEGIN
--This procedure is used to sum all the databases sizes in all the Linked servers.</pre>
IF OBJECT_ID('tempdb.dbo.#sum_volum') IS NOT NULL
BEGIN
DROP TABLE tempdb.dbo.#sum_volum;
END
ELSE
BEGIN
CREATE TABLE #sum_volum (
Server_name varchar(50),
DATABASE_NAME varchar(500),
DATABASE_SIZE bigint
)
END
DECLARE @tmp_key varchar(50)
DECLARE @db CURSOR
DECLARE @sql nvarchar(max)
SET @db = CURSOR FOR
SELECT
name 'server_name'
FROM sys.servers
ORDER BY server_id

OPEN @db
FETCH NEXT FROM @db INTO @tmp_key

WHILE (@@FETCH_STATUS = 0)
BEGIN
--select @tmp_key
SET @sql = N'insert into #sum_volum(DATABASE_NAME,DATABASE_SIZE)

select
db.name ,
DATABASE_SIZE = convert(bigint, convert(bigint, sum(s_mf.size))*8)
--REMARKS = convert(varchar(500),null)
from ' + QUOTENAME(@tmp_key) + '.master.sys.master_files s_mf right outer join ' + QUOTENAME(@tmp_key) + '.master. sys.databases db on s_mf.database_id = db.database_id
where
s_mf.state = 0
group by db.name
order by 1 ';

EXEC sp_sqlexec @sql
SELECT
@tmp_key
UPDATE #sum_volum
SET Server_name = @tmp_key
WHERE Server_name IS NULL
OR Server_name = ''
FETCH NEXT FROM @db INTO @tmp_key

END;

CLOSE @db
DEALLOCATE @db
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SUM_DB_Volumes',
@recipients = 'youremail@gmail.com',
@subject = 'SQL servers Database Volume Reports',
@body = 'Please Find the attachment for the Volume reports',
@execute_query_database = 'YourDBName',
@query = '
select server_name,DATABASE_NAME,sum(DATABASE_SIZE) ''DATABASE_SIZE''
from #sum_volum
group by server_name,DATABASE_NAME,DATABASE_SIZE
union all
select '''',''Total Size In KB'',sum(DATABASE_SIZE)
from #sum_volum
union all
select '''',''Total Size In GB'',((sum( DATABASE_SIZE )/1024)/1024)
from #sum_volum
union all
select '''',''Total Size In TB'',(((sum( DATABASE_SIZE )/1024)/1024)/1024)
from #sum_volum',

@attach_query_result_as_file = 1,
@query_result_separator = ' ',
@exclude_query_output = 1,
@query_result_no_padding = 1,
@query_result_header = 1,
@query_attachment_filename = 'SQL server volume report.csv'
END

GO 

 

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

T-SQL Script to find Free space By Database

This is one of the handy script to find the free space by each database. This is useful when you troubleshoot the database space issues.

 DECLARE @DatabaseInfo TABLE
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime)</pre>
DECLARE @command VARCHAR(5000)

SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles'
INSERT INTO @DatabaseInfo
(ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate)
EXEC sp_MSForEachDB @command

SELECT
ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate
FROM @DatabaseInfo
ORDER BY FreeSpacePct DESC,
ServerName,
DatabaseName 

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

 

 

Enable Instant File Initialization is easy in SQL Server 2016

Starting from SQL Server 2005, Microsoft introduced a feature called Instant File Initialization. By default, when you create a database, add a new data file, increase the size of an existing file,  in SQL Server first initialized by filling the files with zeros.

File initialization causes these operations to take longer. However, when data is written to the files for the first time, the operating system does not have to fill the files with zeros. To accomplish this we need to enable “Instant File Initialization” in SQL Server.  Prior to SQL Server 2016, to enable this feature you had to edit the Local Security Policy to give the account that runs the SQL Server service the “Perform volume maintenance tasks” right as shown in Figure 1

  1. Run secpol.msc on the server.
  2. Expand the Local Policies Folder
  3. Click on User Rights Assignment
  4. Go down to the “Perform Volume Maintenance Tasks” option and double click it
  5. Add your SQL Server Service account, and click OK out of the dialog.

Secpol

Figure 1

Microsoft made this feature easy in SQL Server 2016, When you install SQL Server you have a choice to enable this feature under server configuration as show in Figure 2. You do not have to configure after installation, once you enable it will automatically grant required permission to the service account the SQL Server installed.

screen.png

Figure 2

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

Merge Statement to perform insert , update and delete in one statement

This example, demonstrate how to perform insert, update and delete on single statement using MERGE. If you are a Database Developer/BI expert, you would need to refresh the target table to match the source tables periodically. Prior to SQL Server 2008, you would need to perform this task by writing separate T-SQL logic for insert, delete and update. Starting from SQL Server 2008, you can perform all three SQL Statements (Insert, Update and Delete) in one statement using MERGE Statement.

What is the use of MERGE statement in SQL Server?

Merge statement introduced in SQL Server 2008 allows us to perform inserts, updates and deletes in one statement, which means we no longer have to use multiple statements to perform insert, update and delete.

Basic Merge syntax:

Merge_Statement

To try the example , you’ll need to first run the following script to create and populate the tables used in the examples:

 -- CREATE A SOURCE TABLE
CREATE TABLE [DBO].[STUDENT_SOURCE](
[ID] [INT] NOT NULL,
[STUDENTNAME] [NCHAR](50) NOT NULL,
CONSTRAINT [PK_STUDENT_SOURCE] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] 
 -- CREATE A TARGET TABLE</pre>
CREATE TABLE [DBO].[STUDENT_TARGET](
[ID] [INT] NOT NULL,
[STUDENTNAME] [NCHAR](50) NOT NULL,
CONSTRAINT [PK_STUDENT_TARGET] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] 
 --- INSERT RECORDS INTO SOURCE TABLE
INSERT INTO [STUDENT_SOURCE] VALUES (1,'RAMA')
INSERT INTO [STUDENT_SOURCE] VALUES (2,'SANKAR')
--- INSERT RECORDS INTO TARGET TABLE
INSERT INTO [STUDENT_TARGET] VALUES (1, 'RAMASANKAR')
INSERT INTO [STUDENT_TARGET] VALUES (3, 'MURTHY') 

After you ran the above script, you have two tables(STUDENT_SOURCE and STUDENT_TARGET)  created with some sample data as below.

Tables

Now, I will use MERGE statement to synchronize target table with source.

 MERGE [STUDENT_TARGET] AS T
USING [STUDENT_SOURCE] AS S
ON T.ID = S.ID
WHEN MATCHED THEN
UPDATE SET T.STUDENTNAME = S.STUDENTNAME
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, STUDENTNAME) VALUES (S.ID,S.STUDENTNAME)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
(3 row(s) affected) 

There you go, Three rows are modified which means one update, one Insert  and one Delete performed in single statement.

After executing the Merge statement we can see that both source and target are identical.

 SELECT * FROM STUDENT_SOURCE;</pre>
SELECT * FROM STUDENT_TARGET; 

After_Table

MERGE statement is very useful improvement to update database tables with complex logic. Better Performance and scalability can be achieved with MERGE statement.

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

Tempdb Enhancements in SQL Server 2016

Tempdb Trace Flags 1117 and 1118 are no longer needed in SQL Server 2016. 1117 flag is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE and 1118 flag is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE.

What are these Trace Flags

TRACE FLAG 1117 – GROW ALL FILES IN A FILE GROUP EQUALLY

Trace flag (TF) 1117 is related strictly to file groups and how data files grow within them. A file group is a logical container for one or more data files within a database. TF 1117 forces all data files in the same file group to grow at the same rate, which prevents one file from growing more than others, leading to the hotspot issue described earlier in this chapter. Enabling this trace flag in earlier versions of SQL Server is a minor tradeoff in performance. For example, if you were using multiple data files in user databases, this trace flag affects them as well as TempDB’s data files. Depending on your scenario, that could be problematic—an example would be if you had a file group that you did not want to grow as a single unit. Starting with SQL Server 2016, the behavior to grow all data files at the same rate is built into TempDB by default, which means you no longer need this trace flag.

TRACE FLAG 1118 – FULL EXTENTS ONLY

Administrators use trace flag 1118 to change page allocation from a GAM page. When you enable TF 1118, SQL Server allocates eight pages, or one extent, at a time to create a dedicated (or uniform) extent, in contrast to the default behavior to allocate a single page from a mixed extent. Unlike with TF 1117, there was no potential downside to enabling TF 1118—it is generally recommended for all SQL Server implementations in earlier releases. Starting with SQL Server 2016, all allocations of TempDB pages use uniform extent allocation, thus eliminating the need to use TF 1118.

In earlier versions of SQL Server, the default configuration uses one data file for TempDB. This limitation sometimes results in page-latch contention, in order to mitigate/remedy this behavior in SQL Server is to add more data files, in turn SQL Server creates more of three special types of pages (SGAM, GAM and PFS) and gives more throughput to TempDB. Importantly, these files should all be the same size.  Creation of multiple data files depends on many factors like number of cores vs CPU sockets, Hyper-threading.  Microsoft suggested in the article KB2154845, about the guidelines/recommendation to reduce allocation contention.

The recommendation in the KB2154845 is now built into product setup in SQL Server 2016. When you install SQL Server 2016, the default configuration of TempDB now adjusts to your environment, as show in the below Figure.

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

Powershell Script to Backup the file system folders/files to Azure blob storage

One of the requirement from the customer was backup the important application files to azure blob storage and delete the backups older than 7 days. I have written a below script to automate the task.

</pre>
#--------------------------------------------------------------------------------
#Script: Backup the files to azure blob storage
#Auther : Ramasankar Molleti
#This will make sure to run the powershell as administrator for Azcopy command
if (!([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")) { Start-Process powershell.exe "-NoProfile -ExecutionPolicy Bypass -File `"$PSCommandPath`"" -Verb RunAs; exit }

Write-Host "started"
#Source of the file
$source = "Path of the files"

#Initializing a destination file
$destination = "Temporary Destinationpath
#Adding current date to the file
$date = Get-Date
$date = $date.ToString("yyyy-MM-dd")
$destination = $destination+$date
$destination =$destination+'.zip'
write-host $destination
Write-Host "Compressing is in progress" -ForegroundColor Yellow
#This assembly is used to compress the folder
Add-Type -Assembly 'System.IO.Compression.FileSystem'
[System.IO.Compression.ZipFile]::CreateFromDirectory($source, $destination,'Optimal',$false)
Write-Host "Compression is done" -ForegroundColor Green
# Backup the compressed Appplication folders/files to blob storage
Write-Host "Begin backup to azure blob" -ForegroundColor Yellow
AzCopy /Source:D:\temp /Dest:https://yourstorageaccount.blob.core.windows.net/Provideyourazurecontainer /DestKey:yourazurblobstorageprimarykeydetails
Write-Host "Complete Backup to AZure Blob" -ForegroundColor Green
#Define the source storage account and context.
$SourceStorageAccountName = "Provide your storage account"
$SourceStorageAccountKey = "Provide your storage account primary/master key"
$SrcContainerName = "Your AzureContainername"
$SourceContext = New-AzureStorageContext -StorageAccountName $SourceStorageAccountName -StorageAccountKey $SourceStorageAccountKey
#$blobs = Get-AzureStorageBlob -Container $SrcContainerName -Context $SourceContext
#Set the threshold value
$isOldDate = [DateTime]::UtcNow.AddDays(-7)
#Get a reference to blobs in the source container.
$blobs = Get-AzureStorageBlob -Container $SrcContainerName -Context $SourceContext | Where-Object { $_.LastModified.UtcDateTime -lt $isOldDate }
$blobs| Remove-AzureStorageBlob
Write-Host "end" 

 

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

Fix – Availability Group Listener Failed with the message WSFC could not bring the network name resource

One of the common issue I and my team have encountered several times when we setup availability group listener in SQL Server as below

Msg 19471, Level 16, State 0, Line 2
The WSFC cluster could not bring the Network Name resource with DNS name ‘<DNS name>’ online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.

Msg 19476, Level 16, State 4, Line 2
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator.

As per the documentation cluster object name should have “Create computer objects” and “Read all properties” permissions within Active Directory.

Reference: https://msdn.microsoft.com/en-us/library/hh213080.aspx#WinPermissions

We have requested the Active directory team to grant “Create Computer Objects” permissions for windows cluster and listener within Active Directory.

After granting the permission, we were able to create availability groups listener from SQL Server.

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

How To Change SQL Server Instance Collation After Installation

Last week one of the support team member contacted me for help how to change the collation without re-installing sql server. Team has installed and configured sql server but forgot to change the collation as per the customer application needs. Good thing about this , it was a brand new install

Here is how we can change the collation of sql server instance without re-installing.

  1. Backup any user databases, jobs (In this case Only one database configured, no jobs yet)
  2. Detach user databases
  3. Determine the Sql server instance collation (In this case Latin1_General_100_CI_AS)
  4. Rebuild the system databases by using the below command

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER

/SQLSYSADMINACCOUNTS=accuntname /[ SAPWD= accountpwd ]

/SQLCOLLATION=Latin1_General_100_CI_AS

 

Reference: https://msdn.microsoft.com/en-us/library/ms179254.aspx

5.  Once the rebuild operation is complete, check the collation to verify whether this change is successful or not. There you go!

 

 

6. Attach all user databases if any which were detached in step 2

7. Change the collation of user databases if any (ALTER DATABASE DBName collate SQL_Latin1_General_CP1_CI_AI)

 

Now we have new instance with new collation setting.

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

Linkedin: Linkedin Profile

Twitter: Twitter