1. Install SQL Server features
In the SQL Server installation wizard, on the Feature Selection page, select the following items:
·
Database Engine Services
·
Integration Services
o Scale Out Master
o Scale Out Worker
On the Server
Configuration page, click Next to accept the default service accounts and startup types.
On the Database
Engine Configuration page, select Mixed Mode and click Add Current User.
On the Integration
Services Scale Out Configuration - Master Node and Integration Services Scale Out Configuration - Worker Node pages, click Next to accept the default settings for the port and
certificates.
Finish the SQL Server installation Wizard.
2. Install SQL Server Management Studio
Download and install SQL Server Management Studio (SSMS).3. Enable Scale Out
Open SSMS (in Administrator Mode) and connect to the local Sql Server instance. In Object Explorer, right-click Integration Services Catalogs and select Create Catalog.
In the Create Catalog dialog, the option Enable this server as SSIS scale out master is selected by default.
4. Enable a Scale Out Worker
In SSMS, right-click SSISDB and select Manage Scale Out.
The Integration Services Scale Out Manager app opens.
1. Open Scale Out Manager from SQL Server Management Studio
Open SQL
Server Management Studio (SSMS) and connect to the SQL Server instance of Scale
Out Master.
In Object
Explorer, right-click SSISDB, and select Manage Scale Out.
2. Open Scale Out Manager by running ISManager.exe
Locate
ISManager.exe under %SystemDrive%\Program
Files (x86)\Microsoft SQL Server\140\DTS\Binn\Management. Right-click ISManager.exe and select Run as administrator.
After Scale
Out Manager opens, enter the SQL Server instance name of Scale Out Master and
connect to it to manage your Scale Out environment.
Tasks available in Scale Out Manager
In Scale Out
Manager, you can do the following things:
Enable Scale Out
After
connecting to SQL Server, if Scale Out is not enabled, you can select Enable to enable it.
View Scale Out Master status
The status of
Scale Out Master is shown on the Dashboard page.
View Scale Out Worker status
The status of
Scale Out Worker is shown on the Worker Manager page. You can
select each worker to see the individual status.
Add a Scale Out Worker
To add a Scale
Out Worker, select + at the bottom of the Scale Out Worker list.
Enter the
computer name of the Scale Out Worker you want to add and click Validate. The Scale Out Manager
checks whether the current user has access to the certificate stores on the
Scale Out Master and Scale Out Worker computers
If validation
succeeds, Scale Out Manager tries to read the worker server configuration file
and get the certificate thumbprint of the worker. For more info, see Scale Out Worker. If Scale Out Manager can't
read the worker service configuration file, there are two alternative ways for
you to provide the worker certificate.
1.
You can enter the thumbprint of worker certificate directly.
2.
Or, you can provide the certificate file.
After
gathering information, Scale Out Manager describes the actions to be performed.
Typically, these actions include installing the certificate, updating the
worker service configuration file, and restarting the worker service.
In case the
worker certificate is not accessible, you have to update it manually and
restart the worker service.
Select
the Confirm checkbox and then select OK to start adding a
Scale Out Worker.
Delete a Scale Out Worker
To delete a
Scale Out Worker, select the Scale Out Worker and then select - at the bottom of
the Scale Out Worker list.
Enable or disable a Scale Out Worker
To enable or
disable a Scale Out Worker, select the Scale Out Worker and then select Enable Worker or Disable Worker. If the worker is
not offline, the status of the worker displayed in Scale Out Manager changes
accordingly.
Edit a Scale Out Worker description
To
edit the description of a Scale Out Worker, select the Scale Out Worker and
then select Edit. After you finish editing
the description, select Save.
5. Run packages in Scale Out
Run packages with the Execute Package in Scale Out dialog box
- Open the Execute Package In Scale Out dialog box.In SQL Server Management Studio, connect to the Integration Services server. In Object Explorer, expand the tree to display the nodes under Integration Services Catalogs. Right-click the SSISDB node or the project or the package you want to run, and then click Execute in Scale Out.
- Select packages and set the options.On the Package Selection page, select one or more packages to run. Set the environment, parameters, connection managers, and advanced options for each package. Click a package to set these options.On the Advanced tab, set a Scale Out option called Retry count to specify the number of times a package execution will retry if it fails.
- Select worker computers.On the Machine Selection page, select the Scale Out Worker computers to run the packages. By default, any computer is allowed to run the packages.
- Run the packages and view reports.Click OK to start the package executions. To view the execution report for a package, right-click the package in Object Explorer, click Reports, click All Executions, and find the execution.
Run packages with stored procedures
- Create executions.Call
[catalog].[create_execution]for each package. Set the parameter @runinscaleout toTrue. If not all Scale Out Worker computers are allowed to run the package, set the parameter @useanyworker toFalse. For more info about this stored procedure and the @useanyworker parameter. - Set execution parameters.Call
[catalog].[set_execution_parameter_value]for each execution. - Set the Scale Out Workers.Call
[catalog].[add_execution_worker]. If all computers are allowed to run the package, you don't have to call this stored procedure. - Start the executions.Call
[catalog].[start_execution]. Set the parameter @retry_count to set the number of times a package execution will retry if it fails.
No comments:
Post a Comment