Containers and Environments

Static Vs. Dynamic Containers

Two types of Containers are supported, Static Containers and Dynamic Containers. Each type uses a different technique to store and identify the set of target databases.

A Static Container consists of a fixed group of databases. Databases are added to the Static Container by specifying the typical connection information, such as the database name and SQL server name (or IP address). To add or remove databases from the Static Container, the user must open the Container Manager and manually edit the Container configuration.

When scripts and queries are run against a Static Container, Combine will retrieve the identifiers (i.e., database and server names) of the target databases from the Container configuration, and then run the scripts on all those databases using the authentication type and credentials entered for the Container. Static Containers are therefore useful to store groups of databases that are relatively "static" (i.e., when databases that belong to the group are not created, dropped, or moved between servers frequently). To demonstrate this fact, consider the following counter example where Static Containers should not be used: A Static Container named "MSDB Databases" consists of all msdb databases over all servers in the production environment, and assume that a new instance of SQL server is installed every day. In order to ensure that the "MSDB Databases" Static Container indeed holds all msdb databases, the user must manually add the msdb database to the Container for each new server, daily. This maintenance overhead can be overcome by using Dynamic Containers.

Note: The main advantage of Dynamic Container is that they allow users to share Environment and Container information from a single data repository. Using Dynamic Containers, developers only need to configure the Environments and Containers in their user-interface once, and a single person can maintain the data repository from that point on.

Dynamic Containers assume that a list of servers and databases is already available in some tables. Throughout, we use the term Repository, or Reference, to denote the database in which the server-to-database mappings reside. When a script is run against a Dynamic Container, Combine first connects to the Reference database and runs a user-provided query that returns the identifiers of all target databases. Then, as in the case of Static Containers, Combine connects and deploys the script on all target databases.

The following steps are required to create a Dynamic Container:

      1. Locate the Reference database and table(s) that holds the server and database information.
      2. Write a query that returns the database and server names for all target databases.
      3. Create a Static Container and add the Reference database to it. The Reference database should be the only database in this Container.
      4. Use the Dynamic Container Wizard to create the Dynamic Container. When prompted, associate the Static Container in (3) and the query in (2) with the Dynamic Container.

    The example below illustrated the concept of Dynamic Containers. Here, we create a Dynamic Container with five DBAMaint target databases on five different servers, and call this Container "DBA Databases". First, a Reference database is required. Assume that the ServerRepository database on ProdSvr5 has the server-database mappings, and that the data is stored in a table named DBServers (the content of the DBServers table is given in Figure 4).

    Figure 3:  Using the ServerRepository Reference database to build the "DBA Databases" Dynamic Container.

    Figure 4:  The server-database mappings in table DBServers on the Reference database.

    Now, either one of the following queries (or many other queries) will return the set of DBAMaint target databases:

      SELECT NameOfServer AS ServerName,
      NameOfDatabase AS DatabaseName
      FROM DBServers
      WHERE IsDBA = 1

      SELECT DISTINCT NameOfServer AS ServerName,
      'DBAMaint' AS DatabaseName
      FROM DBServers

    Next, we create a Static Container (named DBServerMap in Figure 3) that holds the ServerRepository target database. Finally, we create the Dynamic Container using the Dynamic Container Wizard, and when prompted, assign the DBServerMap Container and the query as part of the Dynamic Container configuration. Once the "DBA Databases" Container is created, every time scripts are run against this Container, Combine performs the steps in Figure 5 to deploy code on all DBAMaint target databases.

    Figure 5:  The steps taken by Combine to execute a script against the "DBA Databases" Dynamic Container.

    © 2001-2017 JNetDirect, Inc. All Rights Reserved.