Clustering in SSIS | A Definitive Guide with Best Practices [ OverView ]

Last updated on 07th Jan 2022, Blog, General

(5.0) | 19856 Ratings 1162

Share:

Introduction to SSIS:

This is another section that only applies to the package deployment model. If you can, you should change your SSIS methods to take advantage of the project deployment model. Unfortunately, SSIS is not a clustered service by default. Microsoft does not recommend that you cluster SSIS, as it can produce unexpected results. For example, if you put SSIS in the same cluster group as SQL Server and SQL Server fails, it will cause SSIS to fail as well. Even though it does not cluster in the main SQL Server setup, it can be clustered manually through a series of relatively easy steps. If you decide that you must cluster SSIS, this section walks you through those steps, but assumes that you already know how to use Windows clustering and understand the basic clustering architecture. Essentially, the steps to install SSIS as a clustered service are as follows

SSIS Clustering:

Microsoft’s SQL Server Integration Services is not something that is installed with the instance, it goes to the server as a shared resource. Microsoft has documented on TechNet that SSIS is not cluster aware and as a result they state the following: “Clustering Integration Services is not recommended because the Integration Services service is not a cluster or a cluster-aware service, and support failover from a single cluster.” does not connect to another node.”. Although it is possible to run SSIS in a cluster, it has limitations that must be fully considered and understood. See Configuring Integration Services in a Cluster (SQL Server 2008 R2) and Integration Services (SSIS) in a Cluster (SQL Server 2012) for details.

SSIS on a cluster:

• I thought it was worth a quick post: While many aspects of SQL Server are cluster aware, SSIS is not. Why? My guess is that there isn’t a pressing business requirement for this to be a cluster aware service – while many companies have critical ETL processes in place to help drive the company’s business, this scenario requires failover transactions. Not like the system.


• Simultaneously, I had a customer who was testing a failover of one of his main SQL clusters and suddenly noticed that many more than one task failed to run. In helping issues, I noticed they had SSIS steps in the job pointing to the cluster name to extract packages from MSDB, and more importantly, someone forgot to install the SSIS service on the failover node Was.


• Someone had configured SSIS to point the cluster at the primary node, but may have forgotten to install the service and make the same configuration changes on the failover node – quite an easy fix; But there is also a good education opportunity for the client as they were not familiar with SSIS at all (transitioning to a new environment for them last year, so much of this is new to them).


• It’s not recommended to configure SSIS as a clustered resource, but that doesn’t mean it can’t be used on a clustered SQL Server – you just need to know what you’re doing and What are the results. In my current scenario, we have configured SSIS to work on a clustered environment, but not as a cluster resource (the second link below will show you how).

Learn Advanced Microsoft SSIS Certification Training Course to Build Your Skills

Weekday / Weekend BatchesSee Batch Details

Strategy for Dealing with SSIS in Clusters:

• It is common to use Microsoft Server Clustering to run multiple instances of SQL Server. For example, let’s say you have an online instance, an extraction, transformation, and loading (ETL) instance, and a reporting instance. You can serve these SQL Server instances as Cluster Virtual Machines (CVMs) on up to four physical machines in Active/Active/Active/Passive configurations. (See Figure 1) The idle machine observes the status of the other three machines, ready to assume their functions if necessary. (Note that CVMs use the clustering technology that has been present since SQL Server 7.0. They are not related to virtualization platforms, such as VMware ESXi or Microsoft Hyper-V.)


• Each SQL Server instance takes care of different needs, so each instance must be running SQL Server Integration Services (SSIS). However, complications arise when you use SSIS in a cluster because it is machine based rather than an instance like its predecessor DTS. In other words, in a cluster, SSIS runs on a physical machine, when everything else runs on the CVM.

Disadvantage:

• Some of the potential disadvantages of configuring the Integration Services service as a cluster resource include the following. When a failure occurs, the running packages are not restarted.


• You can recover from package failures by restarting the package from the checkpoint. You can restart the service from the checkpoint without configuring it as a cluster resource. For more information, see Restart packages using checkpoints.


• When you configure the Integration Services service in a different resource group than SQL Server, you cannot use Management Studio from a client computer to manage packages that are stored in the msdb database. Integration Services cannot delegate service credentials in this double-hop scenario.


• When you have multiple SQL Server resource groups that include the Integration Services service in one cluster, a failover can lead to unexpected results. Consider the following scenario. Group1, which includes the SQL Server service and Integration Services service, is running on Node A. Group2, which also includes the SQL Server service and Integration Services service, is running on Node B.


• An attempt to start another instance of the IntegrationServices service on node A fails because the IntegrationServices service is a single-instance service. Whether the SQL Server service that is trying to fail on node A also fails depends on the configuration of the Integration Services service in Group 2.


• If the Integration Services service was configured to affect other services in the resource group, the SQL Server service that is failing will fail because the Integration Services service has failed. If the service was configured not to affect other services in the resource group, the SQL Server service would be able to fail on node A. Unless the Integration Services service in group 2 was configured not to affect other services in the resource group. Failure of the Integration Services service that is failing, the SQL Server service may also fail if it fails.

Configure the Service as a Cluster Resource:

For customers who conclude that the advantages of configuring the Integration Services service as a cluster resource outweigh the disadvantages, this section includes necessary configuration instructions. However, Microsoft does not recommend that the Integration Services service be configured as a cluster resource. To configure the Integration Services service as a cluster resource, you need to perform the following tasks.



• Install Integration Services on a cluster.
• To install Integration Services on a cluster, you must install Integration Services on every node in the cluster.
• Configure Integration Services as a cluster resource.
• With Integration Services installed on each node in the cluster, you need to configure Integration Services as a cluster resource. When you configure the Integration Services service as a cluster resource, you can add the service to the same resource group as the SQL Server database engine, or to a different group. The following table describes the possible advantages and disadvantages in selecting a resource group.
• Clustering Integration Services is not recommended because the Integration Services service is not a cluster or cluster-aware service, and does not support failover from one cluster node to another. Therefore, in a clustered environment, Integration Services must be installed and started as a stand-alone service on each node in the cluster

Managing SSIS Security with Database Roles:

SSIS database role:

• If people want to access the catalogue, they need to add it to the SSISDB database. The first step in setting up security is to specify the correct groups/users for the database roles. However there is only one database role: SSIS_admin. Users belonging to this role can do much more than simply leave the catalogue. There isn’t much documentation about this role, but you can check the security of this role in the properties window:


• Apparently, the sysadmins in the catalogue also have full privileges. If you want to give users less permissions, you add them to the SSISDB database, but you don’t assign them to any roles (obviously they are added to the public role). As with all database security, it is advisable to work with groups and not individual users. Although for demonstration purposes, I will be working with a single user in the examples. In the following screenshot, I added a database user to SSISDB with the name Koen:



SSIS Permissions:

If we want to give users the appropriate permissions (without just adding them to the SSIS_admin role), we’ll need to assign them to the correct securables. There are three protected objects in the catalogue: project, environment, and package. Let us clarify with an example:

• In Catalog, two folders are created: FolderA and FolderB. Each folder contains a separate project.
• Using Management Studio, we can easily grant permissions to the user we created earlier.
• Let’s give user koen read permission to folder A. Don’t forget to give the permission to read, otherwise the project inside the folder will not be accessible.



When this user now opens the catalogue, he can only see FolderA, but FolderB is hidden. The user can view projects and packages, but cannot modify or execute them. The Manage Object permission is an important permission. With this permission you can delegate administration of permissions to other users without adding them to the SSIS_admin role. For more information on the different permissions, see the table in the comments section of this MSDN page.




IMPORTANT NOTE: Catalogue Security does not work correctly with SQL Server users. Try to use domain accounts/groups as much as possible. Using SQL Server Authentication results in the following error message: An exception occurred while executing the stored procedure ‘some internal stored procedure’. The error message is: ‘The operation cannot be started by an account that uses SQL Server Authentication. Initiate the operation with an account that uses Windows Authentication.’




A great feature of Catalogue Security is that the catalogue views available in SSISDB are filtered according to the permissions granted to the user. For example, if the user checks the Koen Catalog.Executions view, he can only see information about FolderA for which he has permissions.

Management capabilities:

Integration Services Service Integration Services is a Windows service for managing packages. The Integration Services service is only available in SQL Server:

• Management Studio.
• Running the Integration Services service provides the following management capabilities:
• Starting remote and locally stored packages
• Stopping packages running remotely and locally
• onitoring packages running remotely and locally
• package import and export
• package storage management
• Customising Storage Folders
• Stopping running packages when service is stopped
• View Windows Event Log
• Connecting to Multiple Integration Services Servers

Set the properties of the service:

• The Integration Services service manages and monitors packages in SQL Server Management Studio. When you install SQL Server Integration Services for the first time, the Integration Services service is started and the service’s startup type is set to Automatic.


• After the Integration Services service is installed, you can set the properties of the service by using the SQL Server Configuration Manager or the Services MMC snap-in.


• To configure other important features of the service, including the locations where it stores and manages packages, you must modify the service’s configuration file. To set the properties of the Integration Services service by using SQL Server Configuration Manager


• On the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.


• In the SQL Server Configuration Manager snap-in, locate SQL Server Integration Services in the list of services, right-click SQL Server Integration Services, and then click Properties.



In the SQL Server Integration Services Properties dialog box, you can do the following:

• Click on the Log on tab to view logon information such as account names.
• Click the Services tab to view information about the service such as the name of the host computer and the Integration Services to specify the start mode of the service.
• Click OK.
• On the File menu, click Exit to close the SQL Server Configuration Manager snap-in

Conclusion:

SQL Server Integration Services provides functions to transform and validate data during the load process and changes to insert data into your destination. Instead of creating a Stored Procedure with T-SQL to validate or change data, it is good to know about different SSIS functions and how they can be used.

Packages: The SSIS package is the central component of the SQL Server Integration Services code, and it is the canvas on which you will spend most of your development time. An SSIS package is a collection of one or more operations that are called together.




Task : A task is a single operation within a package. There are dozens of different types of tasks available in SQL Server Integration Services (SSIS).




Component: A component is part of a data pipeline, which represents either a source from which data is retrieved, a destination to which data is written, or a transformation that manipulates or re-shapes data. gives.




Execution: This is the act of implementing, or running, logic in an SSIS package. The package can be executed within a SQL Server Data Tools (SSDT) development environment or directly on a properly configured instance of SQL Server.




Deployment: A deployment is when a fully fledged SQL Server Integration Services (SSIS) project is pushed from a development workstation to an instance of Microsoft SQL Server, where it can be executed either manually or with a scheduling tool such as SQL Server Agent. can be executed through Deployment is typically more complex than copying code from one machine to another, although SQL Server does a good job of hiding that complexity for most deployments.




Project : In SQL Server Integration Services (SSIS), the source code is organised into functional units called projects. A project can contain one package or many packages. In most cases, when deploying SQL Server Integration Services code, the entire project is deployed on the server.




Solution: A solution is a logical set of related projects.




SSIS Runtime Engine: This is the logic that allows the package to run. When you are working with SSIS packages in SSDT, the packages will be executed using SQL Server Integration Services (SSIS) runtime on your development machine. After the code is deployed to Microsoft SQL Server, any execution running on that server will use the server’s SSIS runtime.