If you’re a user of both SQL Elements and SQL Diagnostic Manager, it’s a reasonable bet that you’ll have some overlap of instances being monitored by the two products. Whether you’re using SQL Elements primarily for inventory and tagging purposes, or as a lightweight monitoring tool for the servers not presently covered by SQL Diagnostic Manager, from time to time you may want to copy a block of instances either to or from one product to the other. Fortunately this is done relatively easily with some simple SQL queries to the product repositories.
The basic logic we’ll be using in these examples uses a handy trick you can use in SQL 2005 and up to concatenate result sets into a long string, by using the “for XML Path(”)” command. This is done to simplify the process of copying from Management Studio into the Add Server dialogs for each product, both of which expect semicolon delimited lists. If you’d like to see how to make a delimited list from a column without this trick, take a look at this forum post.
Example 1: Copying all servers from SQL Diagnostic Manager to SQL Elements
This is a very straightforward query – just select all instances from the MonitoredSQLServers table and paste the result in to the Add Server dialog in SQL Elements
use [SQLdmRepository];
select
InstanceName + ';'
from
MonitoredSQLServers
for xml path('');
Example 2: Copying all servers from SQL Elements to SQL Diagnostic Manager
Again, a pretty simple query. Use the IderaCoreRepository for this command.
use [IderaCoreRepository]; select Name + ';' from [Common].[Instances];
Example 3: Copying all servers with a single tag from SQL Diagnostic Manager to SQL Elements
Perhaps you’re only wanting to move a single set of tagged servers over to SQL Elements. This can be done by joining through to the Tags table via the ServerTags table.
use [SQLdmRepository];
select
InstanceName + ';'
from
MonitoredSQLServers m
inner join ServerTags st
on m.SQLServerID = st.SQLServerId
inner join Tags t
on st.TagId = t.Id
where
t.Name = 'Test'
for xml path('');
Example 4: Copying all servers with a single tag from SQL Elements to SQL Diagnostic Manager
This is much the same as Example 3, from the SQL Elements side. This is where things begin to get really interesting, because as you’re adding to SQL Diagnostic Manager, you can easily set the newly added block of servers to have the same tag and to use the same alert template.
use [IderaCoreRepository];
select
i.Name + ';'
from
[Common].[Instances] i
inner join [Common].[InstanceTags] it
on i.[ID] = it.InstanceID
inner join [Common].[Tags] t
on it.TagID = t.ID
where
t.Name = 'Development'
for xml path('');
Example 5: Copying all servers with a single owner from SQL Elements to SQL Diagnostic Manager
You can easily filter the list from SQL Elements either by owner or by location. Again, this can be used to help with setting initial defaults when adding to SQL Diagnostic Manager, or perhaps to help with setting application security in DM.
use [IderaCoreRepository];
select
Name + ';'
from
[Common].[Instances]
where
[Owner] = 'Quality Assurance'
for xml path('');
Both products have protections in place to keep you from inadvertently adding the same server twice as long as they’re the exact same name. You’ll get a little error message letting you know which servers appear to be duplicates, but it won’t cancel the add. If you commonly use aliases in your environment, though, you’ll want to make sure you don’t add the same server twice by different names – both products allow this.
Have any other ideas of why you might want to copy between applications, or filters you’d want to apply? Let me know in the comments below or open a forum thread!