Below are the steps to follow the movement of SSISDB from one environment to another:
-- opening the existing Database master key in SSIDB using password(We got this password from the I drive in production)
OPEN master KEY decryption BY password = 'SSISCatalogDBPassword'
go
-- Removing the additional encryption by service master key of backup instance SSISDB
ALTER master KEY DROP encryption BY service master KEY
go
-- Regenerate the service master forcefully in the restored instance SSISDB. We need to force it to work.
ALTER service master KEY force regenerate;
go
-- Add encryption by the newly regenerated master key of previous step
ALTER master KEY ADD encryption BY service master KEY
go
Venkat's space in the Internet
Musings on Technical, Non-technical things
Sunday 25 September 2022
Monday 19 September 2022
Huge Performance Improvement by going for right column order in Composite Index
We had a situation where we had composite clustering key. The table had ~30 million rows.
Composite clustering key (column1_lessSelective, Column2_MostSelective,Column3_Bit).
The query was performing poorly, as joining the other tables with lessSelective column was slowing down the query.
Changing the order of columns made the query to run pretty fast.
composite clustering key (Column2_MostSelective, column1_lessSelective ,Column3_Bit).
Now, Column2_MostSelective was used in JOINS and result was very high performing query. The time reduced from 9 minutes to 13 seconds.
Friday 13 May 2022
How to create SQL Server linked server against cloudera Impala
Below are the step by step approach for making SQL Server Linked Server to work with Cloudera Impala.
As SQL Server is 64 bit product, we have to install Cloudera Impala 64 bit ODBC driver to make it
working with SQL Server linked service.
We need to follow the below steps:
1. Install Cloudera Impala 64 bit driver in the same SQL Server machine and set up the DSN with below settings.
2. Now, create linked server in SQL Server with below settings. Product name, Datasource should be the same as the name created in DSN.
Now, the impala queries will work fine without issues, using SQL Server linked server. We had unicode data pull issues and we solved it by going for above settings.
Friday 3 September 2021
Check Mirroring Status in SQL Server 2012
We can check mirroring status of the SQL Server 2012 using below command.
SELECT
@@SERVERNAME as Server_Name,
DB_NAME(database_id) as Database_Name,
mirroring_state_desc,
mirroring_role_desc,
mirroring_safety_level_desc
FROM
sys.database_mirroring
WHERE
mirroring_role IS NOT NULL
Getting free space information in SQL Server VM
We can get freespace information in a SQL Server VM using below TSQL command.
SELECT DISTINCT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
volume_mount_point [Disk],
file_system_type [File System],
logical_volume_name as [Logical Drive Name],
CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB], ---1GB = 1073741824 bytes
CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB],
CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %]
FROM sys.master_files
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)
Thursday 15 July 2021
Alternate way for FULL OUTER JOIN
Below is an one more way of doing FULL OUTER JOINs involving multiple tables. This gives a simpler approach, instead of having multiple OR conditions in subsequent tables.
1: DECLARE @a table(id int, data int)
2: DECLARE @b table(id int, data int)
3: DECLARE @c table(id int, data int)
4:
5: insert into @a values(1, 9), (2, 8),(3,7)
6: insert into @b values(2, 6), (3, 5)
7: insert into @c values(1, 4), (2, 5)
8:
9: ;WITH CTE_IDs as
10: (
11: SELECT ID from @a
12: UNION
13: SELECT ID FROM @b
14: UNION
15: SELECT ID FROM @c
16: )
17: SELECT c.ID,t.* FROM CTE_IDs as c
18: cross apply
19: (
20: VALUES((select data from @a where id = c.id),
21: (select data from @b where id = c.id),
22: (select data from @c where id = c.id))
23: ) as t(a_data,b_data,c_data)
Monday 8 February 2021
Saving CSV file in UTF8, Compress and Archive them
We had a requirement, where the CSV files generated by SSIS were not in UTF8. We had to resave in UTF8 format. We also had to compress the group of files and archive the original set of files. We achieved that using Powershell.
$CSVfilesFolder = "C:\CSVFiles\"
$CompressFolder = "C:\CSVFiles\Compress"
$ArchiveFolder = "C:\CSVFiles\Archive"
$filesForZip = Get-ChildItem -Path $CSVfilesFolder -Filter *.csv | Sort-Object LastWriteTime | Select-Object -First $CountofFilesToZip | select fullname | ForEach-Object { $_.FullName }
$newFileName = "$($CompressFolder)\GeneratedZipFile.zip"
#Encoding to UTF8
foreach($file in $filesForZip)
{
$UTF8Only = New-Object System.Text.UTF8Encoding($false)
$FileContent = Get-Content -Path $file
[System.IO.File]::WriteAllLines($file, $FileContent, $UTF8Only)
}
#Generate Zip file
$compress = @{
Path = $filesForZip
CompressionLevel = "Fastest"
DestinationPath = $newFileName
}
Compress-Archive @compress -Force
#Archive the files
foreach($file in $filesForZip)
{
Move-Item $file -Destination $ArchiveFolder -Force
}
Subscribe to:
Posts (Atom)
How to Handle SSIS Database movement from one environment to another
Below are the steps to follow the movement of SSISDB from one environment to another: -- opening the existing Database master key in S...
-
We are using Azure Data Factory to load data from Azure storage blobs to SQL Server on-premises. During data loading, we faced SQL Error: ...
-
Hello World, Through this blog, I am going to ruminate over lots of technical stuff around databases. Hoping to provide quality conten...
-
Below are the steps to follow the movement of SSISDB from one environment to another: -- opening the existing Database master key in S...