SQL Server : Reallocate SSISDB database file

Steps to move SISDB from default location or Reallocate SSISDB database files

First, update catalog location:-

use master
go
alter database ssisdb modify file (name=data, filename='L:\mpdbs001\sqlappdb\SSISDB.mdf', filegrowth=256MB)
alter database ssisdb modify file (name=log, filename='L:\mplog001\sqlapplog\SSISDB.ldf', filegrowth=512MB)
go
alter database ssisdb set offline with rollback immediate
go

Second, copy files to new location :

exec xp_cmdshell 'copy /Y L:\mpsys001\sqlsysdb\SSISDB.mdf L:\mpdbs001\sqlappdb\'
exec xp_cmdshell 'copy /Y L:\mpsys001\sqlsysdb\SSISDB.ldf L:\mplog001\sqlapplog\'

alter database ssisdb set online
go

Third, change database owner if required

use ssisdb
exec sp_changedbowner 'Domain\User'
go

-----------Back up Master Key for database SSISDB ---------

use SSISDB
go
BACKUP MASTER KEY TO FILE = 'L:\mpsys001\Keys\SSISDB_MasterKey' ENCRYPTION BY PASSWORD = 'Password';

Thanks

SQL Server : Differences between SSIS 2008 and 2012

SSIS server: SSIS server is no longer a separate service that you connect to, it now appears as a node in Object Explorer when connecting to a SQL Server database instance

SSIS Catalog, which is a database that stores deployed projects, environments, and package execution logs.  Each SSIS server has one catalog.

SSIS Projects -  which is an encapsulation of multiple packages into a single unit that you build, deploy & execute.

SSIS Environments are fairly self-explanatory – they are a wrapper for all environment-specific information (e.g. Connection Strings) that you want to maintain outside of a package and when you execute a package you have to choose which Environment to execute it against. In short Environments are the replacement for SSIS configurations and they work hand-in-hand with Parameters that are also getting introduced in SSIS code-named Denali. 

Data tap: At any path, capture all the data coming through and output it to a text file to review later.  This is done without having to modify the package.

	SSIS 2008	SSIS 2012
Undo And Redo	No Undo And Redo feature in SSIS 2008	Undo And Redo feature available in SSIS 2012.
SSIS PARAMETERS	SSIS Parameters at package level	SSIS Parameters at the package level, task level and project level.
DQS TRANSFORMATION	No DQS in SSIS 2008.	DQS Transformation is available in SSIS 2012.
Change Data Capture (CDC)	Introduced in SSIS 2008. But there is no task to support CDC in SSIS 2008.	CDC ControlTask available to support CDC in SSIS 2012.
Data Flow Tap	No Data Tap Available in SSIS 2008.	Data Tap Available in SSIS 2012.
Deployment	Package Level Deployment	Project Level Deployment Package Level deployment
Logging	Disabled by default	Enabled by default and improved
Newly introduced	-	SSIS server SSIS Catalog SSIS Environments SSIS Parameters Shared Connection Managers Offline Connection Managers
Improved	-	Column mapper New Reports included [catalog].[execution_data_statistics]   Debug the Script component by setting break points
Execution	runs package locally and uses local components like drivers	When you use the “Run Package” command in SSMS, the package runs on the server,
Version control	-	Each Package is tracked and can be rolled back

Warm Regards,

Chhavinath Mishra 

Sr. Specialist Database Administrator

SQL Server SSIS : Create the SSIS Catalog which is must Before you can deploy the projects to the Integration Services

I would like to highlight that There have been some rather significant changes made to SSIS in SQL Server 2012 and One of which is project/package deployment. In SQL Server 2005 / 2008, one could easily deploy packages to the MSDB but In 2012, this has been changed. Before you can deploy anything, there has to be an integration services catalog.

Create the SSIS Catalog :

As soon as you are done with design and test packages part in SQL Server Data Tools, you can deploy the projects that contain the packages to an Integration Services server. Before you can deploy the projects to the Integration Services server, the server must contain the SSISDB catalog coz the installation program for SQL Server 2012 does not automatically create the catalog; you need to manually create the catalog by using the following instructions.

You can create the SSISDB catalog in SQL Server Management Studio.

To create the SSISDB catalog in SQL Server Management Studio:

1. Open SQL Server Management Studio.
2. Connect to the SQL Server Database Engine.
3. In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.
4. Click Enable CLR Integration.

    The catalog uses CLR stored procedures.

5. Click Enable automatic execution of Integration Services stored procedure at SQL Server startup to enable the catalog.startup stored procedure to run each time the SSIS server instance is restarted.

Note:    The stored procedure performs maintenance of the state of operations for the SSISDB catalog. It fixes the status of any packages there were running if and when the SSIS server instance goes down.

6. Enter a password, and then click Ok.

The password protects the database master key that is used for encrypting the catalog data. Save the password in a secure location. It is recommended that you also back up the database master key. 

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)