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
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.
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 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 g...
-
This blog post talks about my problems with installing SQL SERVER 2008 Developer Edition in my Windows XP box. After two failed attemp...
-
Below are the steps to follow the movement of SSISDB from one environment to another: -- opening the existing Database master key in S...