Database targeting options
Override file
In order for SQL Build Manager to do its job, you need to tell it what databases you want to update. This is done by sending it list in the form of an --override file.
File Format
The --override file consists of one target per line in the format of:
<server name>:client,<target database>
for example:
sqllab1.database.windows.net:client,SqlBuildTest007
sqllab1.database.windows.net:client,SqlBuildTest008
sqllab1.database.windows.net:client,SqlBuildTest009
sqllab1.database.windows.net:client,SqlBuildTest010
Optionally, if you need to set a concurrency value other that the target server (see Concurrency Options for how to leverage the Tag and MaxPerTag settings), you can add a tag value to the line in the format of:
<server name>:client,<target database>#<tag value>
for example:
sqllab1.database.windows.net:client,SqlBuildTest007#Tag1
sqllab1.database.windows.net:client,SqlBuildTest008#Tag1
sqllab1.database.windows.net:client,SqlBuildTest009#Tag2
sqllab1.database.windows.net:client,SqlBuildTest010#Tag2
Why client? Inside the .sbm file, there is a default database target set to client. If you don’t provide an override, it will look for a database of that name. So, that combination of client,target tells the builder to substitute the database name client with the database name target at runtime.
This is why the flag name is --override!
### File Creation from a SQL Query
Using the sbm utility override command, you can create an override file from a SQL query. This is useful when you want to target a specific set of databases based on some criteria. For example, you may want to target all databases that have a name that starts with Prod or Dev. You can do this with a SQL script such as:
SELECT [ServerName] + ':client,' + [DatabaseName]
or
SELECT [ServerName] + ':client,' + [DatabaseName] + '#' + [TagValue]
Runtime
If the --override setting is provided but there isn’t a --servicebustopicconnection value, the runtime will use this file directly to update the database and/or distribute traffic for threaded and batch. It is important to also understand the impact of concurrency options.
NOTE: The Kubernetes sbm k8s and ACI sbm aci commands only uses the Service Bus Topic option at runtime.
Service Bus Topic
Background
If the value for --servicebustopicconnection is set or there is a value for ServiceBusTopicConnectionString in the settings JSON file (for batch) or secrets YAML file (for k8s pods) or Azure Key Vault (for Batch, Kubernetes and Azure Container Instance), the runtime will look at the Service Bus Topic for messages that contain the override targets.
In order to get the messages into Service Bus, you must first enqueue the targets with the sbm batch enqueue, sbm k8s enqueue, or sbm aci enqueue command, passing in the --override target file.
Here it is important to also understand the impact of concurrency options when enqueueing. If you specify the --concurrencytype value of Server or MaxPerServer the messages are added to a Session enabled Topic subscription so that targeting SQL servers can be controlled. Using the Count setting adds the messages to a subscription that is not session enabled to ensure unrestricted message distribution.
Advantages
A key advantage for using a Service Bus Topic is your ability to easily monitor the progress of the batch run by watching the message count in the Topic. You can use the Service Bus Explorer app or the Service Bus Explorer found in the Azure portal for your Service Bus. When running in Kubernetes, you can should use the sbm k8s monitor command.
In addition, it will allow for more even distribution of targets by allowing nodes/pods to not go idle as they would if using an override file and they exhaust their target list.
Queue Runtime
When executing a run that leverages Service Bus Topics, there are two key considerations.
--concurrencytype- this must match the value used with enqueueing the messages. If it does not, the runtime will not be able to locate the messages (see background above regarding the targeted subscriptions)--jobname- this must match the value used with enqueueing the messages. To ensure that a run only picks up targets that were intended for it, it checks for this value on the message. If the Label on the message does not match the batch job name, that message will be sent to the DeadLetter queue and not processed.