Dec
20
2012

Collation of character fields in temporary objects

It's a common practice to use scalar variables, table variables, and temporary tables when developing a database application. If these transient objects contain (or are) character fields, which collation do they have if a collation isn't explicitly specified?

 

Test Setup

The idea of the test is to demonstrate how collation conflicts are possible when using temporary objects.

First, we'll create a new database with a collation that differs from the instance collation. I've chosen one randomly to make it easy to pick out for the purposes of demonstration. The collation of the instance I'm testing on is SQL_Latin1_General_CP1_CI_AS, which is probably the most common.

CREATE DATABASE CollationTest COLLATE Thai_CS_AI;
GO

USE [CollationTest];
GO

Next, we'll create a base table with a couple of character fields in it, and a row of data to test with:

CREATE TABLE [dbo].[Table1]
(
	/* Inherit the default collation */
	Col1 varchar(20),

	/* Same as the instance collation */
	Col2 varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS
);
GO

INSERT INTO [dbo].[Table1](Col1, Col2) VALUES (N'a', N'b');
GO

Let's verify Col1's collation:

SELECT
	collation_name
	FROM sys.columns
	WHERE
		(object_id = OBJECT_ID(N'[dbo].[Table1]')) AND
		(name = N'Col1');

Running that query returns Thai_CS_AI, which was inherited from the database collation.

 

Scalar Variables

It turns out that there's no way to get a collation conflict using a scalar variable unless we explicitly use COLLATE on both sides of the comparison expression, like so:

DECLARE @v1 varchar(20) = 'B';

SELECT * FROM [dbo].[Table1]
	WHERE Col2 COLLATE Thai_CI_AI = @v1 COLLATE Thai_CS_AI;

The reason why is because of collation precedence: scalar variables (and string literals, by the way) are always considered to be coercible-default. In other words, the comparison is done using the collation of the field on the other side of the expression:

DECLARE @v1 varchar(20) = 'B';

SELECT * FROM [dbo].[Table1] WHERE Col2 = @v1;

The above query returns a row because the comparison used the case-insensitive SQL_Latin1_General_CP1_CI_AS collation.

It's interesting to note that the collation of a scalar variable can't be explicitly set in its definition; it will always inherit the database collation.

 

Table Variables

Since we know that table variables are actually backed by tempdb, we can now run some tests where the collation of the object doesn't always get clobbered by the comparison field.

Let's try comparing two fields that have the same collation label (implicit):

DECLARE @t table (Col1 varchar(20));

SELECT * FROM [dbo].[Table1] a INNER JOIN @t b ON b.Col1 = a.Col1;

This succeeds. We're comparing a Thai_CS_AI field in the base table with a table variable whose field Col1 has inherited the same collation from the database.

Now let's try two different collations by comparing against Col2 in the base table, and see what happens:

DECLARE @t table (Col1 varchar(20));

SELECT * FROM [dbo].[Table1] a INNER JOIN @t b ON b.Col1 = a.Col2;

Now we get the error, "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Thai_CS_AI" in the equal to operation." In this case, the two collations are different and at the same precedence level. SQL Server can't simply give one collation precedence over the other, and it throws up its hands in disgust.

 

Temporary Tables

As mentioned in the link in the previous section, there are many similarities and differences between table variables and temporary tables. Let's find out where character field collation fits in. We'll run the exact same tests as in the table variable section, using a temporary table instead.

CREATE TABLE #t (Col1 varchar(20));

SELECT * FROM [dbo].[Table1] a INNER JOIN #t b ON b.Col1 = a.Col1;
GO

DROP TABLE #t;

Now we get a collation conflict error. What? It turns out that temporary tables are different than scalar variables in this respect -- the field collations now inherit from tempdb instead of from the current database. (Edit 2013-02-10: Thanks to Aaron Bertrand for pointing out that temporary tables created in the context of a contained database inherit the collation from the current database, not from tempdb.)

Running the second test now succeeds because both fields have an implicit collation of SQL_Latin1_General_CP1_CI_AS (since that is my instance, and hence tempdb, collation):

CREATE TABLE #t (Col1 varchar(20));

SELECT * FROM [dbo].[Table1] a INNER JOIN #t b ON b.Col1 = a.Col2;
GO

DROP TABLE #t;

 

DATABASE_DEFAULT

Because of this crazy behaviour with temporary tables, a virtual collation DATABASE_DEFAULT was created to say that the field should use the collation of the current database, instead of inheriting from the database where the field was created.

Here's the first query from the previous section again, with the special collation added:

CREATE TABLE #t (Col1 varchar(20) COLLATE DATABASE_DEFAULT);

SELECT * FROM [dbo].[Table1] a INNER JOIN #t b ON b.Col1 = a.Col1;
GO

DROP TABLE #t;

And this succeeds like we would expect.

 

Conclusion

When working with table variables and temporary tables, it's really important to know the similarities and differences between the two. In the case of character field collations, simply switching between the two types of tables (which is often done when doing performance tuning) can potentially have bigger side-effects than were intended!

Even though character fields in table variables always inherit the collation of the current database, I would strongly consider explicitly specifying the DATABASE_DEFAULT collation on those fields anyway (assuming this is appropriate), so that the data/business logic will still be the same if someone decides to go and switch things up when performance tuning.

Dec
18
2012

T-SQL Code Review Checklist

In software development, code review is a simple and effective way to reduce the number of bugs that creep in. Merely having a second pair of eyes look at a piece of code can often reveal logic that was implemented incorrectly or weakly, or misses use cases never considered. (Test-driven development is a separate discussion entirely; T-SQL unit testing is not the easiest thing in the world to do, and compromises sometimes have to be made, usually due to time constraints or politics.)

There are many good reasons why code review is a good practice, and I'm not going to get into that here. You can use Google as well as I can, or you can start by reading some of these questions on Programmers Stack Exchange. The purpose of this post is to consolidate many of the different things I look at when doing code review, or even when I'm looking over my own code. While this won't be a complete list, and the items will vary somewhat depending on your company's environment, it should be a good start.

In no particular order:

  • Syntax, including version-specific language use. This seems really obvious, but it can be easy to slip up. For example, if you normally do your development on a 2008+ server and the application still has to support 2005, it's easy to write something like DECLARE @i int = 0 and only have this get caught during testing. (Testing is done against all versions of the database engine that are supported by the application, right? ...right?) In terms of outright syntax, pay particular attention to T-SQL that's contained in string variables, meaning dynamic SQL, or SQL statements embedded in client applications, as these aren't validated until they are run through the containing code. Do all INSERT statements have a column list, and are all required columns specified?
  • Data/business logic protection. This could apply to either designing tables (which I do recommend code-reviewing, too), or when writing code to manipulate data (in the tables or otherwise). Are there enough constraints on the tables to prevent invalid data from entering the system? Are there any logical gaps in a piece of code? Does the piece of code accomplish the prescribed task? If in doubt, ask the developer what was intended. Sometimes logical gaps can be okay if a condition is rare, and the code is appropriately guarded. These same ideas apply to things like procedure parameters, particularly if any of the parameter values come from application user input.
  • Dynamic SQL. Follow through all logic paths to make sure the statements are being concatenated correctly for both syntax and potential injection attack vectors. Make sure the statements are parameterized appropriately.
  • Code Cleanliness. This is a bit tough to define. I personally don't expect strict adherence to a style guide, for example, but I do expect code to be legible, and make appropriate use of indentation, line breaks and comments. Everyone has their own natural style -- and even that can change over time -- so I think giving people more or less free reign over style is fine. Again, as long as the code is legible to others, because that is the most important thing.
  • Version- or edition-specific feature use. This shouldn't come up, as it should have been decided during the design phase, but at the end of the day, a developer can check anything in to the version control system.
  • Use of unexpected methods to accomplish a task. Does the code use a Rube-Goldberg-like approach to accomplish the task? Are cursors being used when all that's necessary is a slightly more difficult to write set-based operation? In these types of cases, I will always go back to the developer and find out why this kind of approach was taken, and what was intended to be accomplished. Most of the time, this kind of situation provides a great learning opportunity.
  • Query plan reuse. Does the code reuse (or not reuse) query plans as expected? Pay particular attention to dynamic SQL: for example, there can be subtle string concatenation bugs where two generated statements (via different code paths) only differ by whitespace, but this is enough to produce a separate query plan. Sometimes dynamic SQL is used to force the creation of an alternative plan for the expected data size -- does this work as expected?
  • Performance. Does the code meet the performance requirements, if any, and/or does it execute in a "reasonable" amount of time with a "reasonable" amount of resource usage? Does the code use table variables or temporary tables, and how does this interact with the query plan(s)? Does the code produce efficient query plans that will scale? It's important to test code on a real data set to find out these things. Some of this is technically a "testing" activity; however, I would say it's unlikely a testing department will have the technical skill required to determine if the code is doing the right thing. Reading and understanding query plans is a huge topic in itself; in short, look for inefficient operators that are unexpected, such as table scans (particularly as the bottom operator in a nested loops join), huge row counts (thick arrows), missing indexes, etc. Speaking of indexes, determine if the code would benefit from index changes, even if query performance is currently acceptable. Even if you don't make those changes now, record your findings, as it could be valuable information to have later if performance does become a problem. This could potentially save an incredible amount of time and effort doing a workload analysis.
  • Deprecated features. This is fairly broad, as deprecation can happen for a wide range of things, from major features (i.e., database mirroring) to minor T-SQL syntax (i.e., using HOLDLOCK without surrounding parentheses). That said, for every new release of SQL Server, it's a good idea to thoroughly read the official list of deprecated features, and make sure no new code is using any of it unless absolutely necessary (and in that case, there needs to be a plan to modify the system to remove the deprecated feature usage). I have a script on the Scripts & Code resource page that dumps the internal performance counters that track usage of deprecated features, which I think may be more broad than the official reference list. This method is great if you want to find out what your application is really doing in production, so you can proactively fix it long before it becomes a problem.

That's a pretty good start! If you think I've left out something important, let me know in the comments and I'll add it to the post, as I want this to be a good reference guide.

Dec
5
2012

Using table-valued parameters in SSIS

Table-valued parameters (TVPs) are a great way to move chunks of data between your application and SQL Server, while still retaining the ability to abstract database functionality with stored procedures and functions.

TVPs were introduced in SQL Server 2008, but I feel they may be underused, not known, or only just now being put on the table for the developers of vendor applications where support for SQL Server 2005 is finally being discontinued.

I'm putting this post out there for DBAs, because while TVPs are relatively easy to implement as a programmer, not all DBAs have the same set of programming skills. There are many, many good reasons for a DBA to learn the .NET framework in at least one modern language (probably C#), but that's a topic for another post. The point here is to put TVPs in the context of SSIS, where they can be very useful. The only reason this is remotely tricky is because TVPs are not exposed in SSIS directly -- you have to implement them yourself in code.

 

The first thing we need to do is set up some database objects to play with. The following script creates a user-defined table type (which is the table-valued parameter type), a table into which we'll dump some data, and a stored procedure that has a TVP, which we'll call from our SSIS package.

CREATE TYPE [dbo].[TestTable] AS table
(
	Column1 varchar(10) NOT NULL,
	Column2 varchar(10) NOT NULL
);
GO

CREATE TABLE [dbo].[TestData]
(
	Id int IDENTITY PRIMARY KEY,
	Col1 varchar(10) NOT NULL,
	Col2 varchar(10) NOT NULL
);
GO

CREATE PROCEDURE [dbo].[TestProcedure]
(
	@testData [dbo].[TestTable] READONLY
)
AS
BEGIN

	SET NOCOUNT ON;

	INSERT INTO [dbo].[TestData](Col1, Col2)
		SELECT
			Column1,
			Column2
			FROM @testData;

END
GO

That's pretty straight-forward. All we're going to do is accept a table as a parameter to the stored procedure, and insert the rows of that table parameter into the base table.

In order to use the procedure, we have to write a little bit of code. Essentially what we must end up with is our data in a DataTable object, which we'll pass as a parameter when we call the stored procedure. There are many different ways to do this in SSIS, depending on where our data is coming from, and what, if anything, we need to do with the data before it gets sent off to the stored procedure.

In this example, I chose to use a Script Component destination inside a Data Flow task. If you're following along at home, the design surface should look like this, noting that the connection manager must be an ADO.NET Connection Manager:

 

The Target Connection points to our testing database, wherever it was landed.

In the ADO.NET Source, I chose to fabricate some junk data using this query:

SELECT 'a' AS c1, 'b' AS c2 UNION ALL
SELECT 'c', 'd' UNION ALL
SELECT 'e', 'f'

Super junky.

There are two other settings to change in the Script Component before we start coding. First, select both input columns on the Input Columns tab:

and then expose the Target Connection connection manager:

Now we're ready to write the code that calls the stored procedure. Edit the script of the Script Component and update the methods as follows:

using System.Data.SqlClient;  // Add this

...

public class ScriptMain : UserComponent
{
    private DataTable _data = null;  // Add this

    public override void PreExecute()
    {
        base.PreExecute();

        _data = new DataTable();

        // This matches the schema of the user-defined table type
        _data.Columns.Add("Column1", typeof(string));
        _data.Columns.Add("Column2", typeof(string));
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Make sure the order of the values matches the schema above
        _data.Rows.Add(new object[] { Row.c1, Row.c2 });
    }

    public override void PostExecute()
    {
        base.PostExecute();

        // Create a connection
        using (SqlConnection conn = (SqlConnection)Connections.Target.AcquireConnection(null))
        {
            try
            {
                // Open the connection
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                // Prepare a stored procedure command to execute
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "[dbo].[TestProcedure]";

                // Add the parameters for the command
                cmd.Parameters.AddWithValue("@testData", _data);

                // Execute the stored procedure
                cmd.ExecuteNonQuery();
            }
            finally
            {
                // Clean up
                if (conn != null)
                    Connections.Target.ReleaseConnection(conn);
            }
        }
    }
}
  • The PreExecute method creates the DataTable object with a schema that matches the user-defined table type.
  • The ProcessInputRow method is called once for each incoming row to the Script Component, so we add a row to the DataTable for every input row.
  • In PostExecute, we connect to SQL Server and call the stored procedure. The magic happens in the cmd.Parameters.AddWithValue method -- it's smart enough to realize that we passed in a DataTable instead of a simple type, and automatically handles sending the data to SQL Server. How it does this I will leave as an exercise for the reader to discover using Profiler.

 

As I said before, there are many different ways to incorporate this code into an SSIS package. It's not a lot of code, but may be a little bit tricky for those who aren't fluent .NET programmers.

Nov
27
2012

Tuning diagnostic queries

Most of the time when we talk about tuning, it's related to improving the performance of a production workload. Maybe (hopefully) we've gone as far as tuning more "hidden" code like stored procedures, triggers, and the black boxes that are user-defined functions. But what about the other queries we run against our production servers?

Maybe we use 3rd-party management/monitoring software, or in-built tools like Activity Monitor or Management Data Warehouse (aka Data Collector) that periodically checks up on our servers. In these cases, if we identify these systems as a performance problem, we're pretty much stuck with either reducing the polling frequency, abandoning the use of the system entirely, or pestering the vendor until the problem is fixed. That's pretty straight-forward.

There's another class of queries we run against our production servers, though: diagnostic queries. If a server is in performance trouble, or we need to go troubleshooting for blocking, or even if we're proactively searching for issues, we automatically whip out our trusty folder of scripts and start running queries to start to narrow things down; queries that could potentially make the problem worse by either adding extra load to the server, or even adding unnecessary time to the troubleshooting process.

It's impossible, of course, to eliminate the need for the database engine to do some processing to return the results of our queries (the observer effect), but how much impact are our queries having on the server? Maybe in the grand scheme of things, not a lot. During a troubleshooting session, however, things are very different: people are anxious for answers, the problem needs to be solved now, and we're working as hard as we can to not only fix the problem, but above all else, to not make things worse.

Now, I'm going to give an example, and I have to apologize in advance to Paul Randal and/or Glenn Barry because I'm about to single them out. Sorry, guys.

A good while back, Paul posted an excellent diagnostic query to analyze wait stats (note: I never saw Glenn's original query), and I had stolen borrowed used it to troubleshoot some issues in the environment I work in. Of course, I played around with it before that, to see it was indeed a useful script to keep around. But when I ran it against the production server in peril, I was floored because it took so long to return the results I needed to see. I was also puzzled that after I reset the wait stats and then ran the query a few seconds later, the results... just didn't look right for what I was expecting. What was going on? It was just a relatively simple SELECT statement from a DMV, which is usually pretty quick. I turned on STATISTICS IO and STATISTICS TIME and ran the query again:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(16 row(s) affected)
Table 'Worktable'. Scan count 7, logical reads 4664, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 812 ms,  elapsed time = 3469 ms.

Shock and/or awe. I think we found the problem! The diagnostic query was consuming a tremendous amount of resources just to tell us that the system was having its resources consumed tremendously.

Well we've got to do something about that, haven't we? Ultimately the query got pulled apart, and using a pre-computation approach involving table and scalar variables, this is where it ended up:

SQL Server parse and compile time: 
   CPU time = 16 ms, elapsed time = 22 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table '#58739F6F'. Scan count 0, logical reads 92, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 192, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 3 ms.
Table '#58739F6F'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(16 row(s) affected)
Table '#58739F6F'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Much better. (You can find the modified script on the Scripts & Code Resources Page. Note that it doesn't do exactly the same thing as the original.)

So what can we learn from this?

  • While DMVs are usually fast, doing further analysis on the results can be expensive, depending on what we do, of course.
  • Test diagnostic queries for performance just as much as for functionality.
  • Fully test diagnostic queries long before they get thrust into the heat of battle. This is sword-sharpening for DBAs.
  • For the scripts that just... perform poorly... and there's nothing more we can do about it, add comments to the script body that say as much. Whoever is about to run the script should be made aware that the query will take some time to finish. The warning will prevent situations like worrying about whether or not it's working, or it even may alert the person to not run the script at all and use a different/similar method instead.
Nov
21
2012

Registered Servers and Central Management Server: Setup

Edit 2013-10-14: This post is now available in video form.

Whether you manage one SQL Server instance or hundreds, you need to have a way to keep track of them all.

Just like best-practices when it comes to security, it would be nice to create logical groups of instances and manage them based on their group membership. Perhaps also inspect, or perform actions against groups of instances:

  • Run queries, including management-type activities like creating or running jobs
  • Check if the settings match a pre-determined configuration, and if not, fix the settings

While there isn't really an inbuilt group-based way to manage these kinds of things, there are two out-of-the-box solutions to mimic many of the advantages, and which help you accomplish these tasks. These solutions are, of course, Registered Servers, and Central Management Server.

In terms of requirements, Registered Servers was available starting with the Management Studio packaged with SQL Server 2005, and support for registering an instance as a Central Management Server was added in SQL Server 2008. I don't believe there's any limitation on version of the instances you can register within either system (and I can't find it in the documentation); off the top of my head I would say SQL Server 2000 and higher sounds pretty safe. From personal experience, 2005 and higher definitely works.

The UI to access both of these features is through the View | Registered Servers menu item in Management Studio. While it might be a bit strange that both of them are contained in one UI, we'll see in a second that that's actually an advantage, because it's likely you'll need to use both in your environment.

 

What is a Registered Server?

While Registered Servers and Central Management Server have different names, both ultimately do the same thing: store a user-defined hierarchy of SQL Server instance connection strings. In other words, a tree view that lists and categorizes your instances. The behind-the-scenes way this is accomplished is very different between the two systems, and this means there are advantages and disadvantages to each.

Although both names contain the word "servers," what we're actually dealing with are instances of SQL Server. I guess it was a design decision by the SQL Server team to call them Registered Servers instead of Registered Instances. It's also a bit confusing because Registered Servers refers to one user-defined hierarchy, while Central Management Servers (note the plural) would refer to multiple hierarchies.

 

Differences

The main difference is that a Registered Servers list is only visible to one Windows user, while a Central Management Server list is publicly available to anyone with enough permissions to view the list.

The best way to show all the differences is probably just to put them in a table, so let's do that:

  Registered Servers Central Management Server
Storage Method XML file in Windows roaming profile msdb database
List Visibility Current user only Everyone who is authorized (view nothing, view all, edit all)
Security of Instance List Based on Windows user context Managed by SQL Server permissions, and msdb database roles
Types of Instances Database engine, SSAS, SSIS, SSRS Database engine only
Connection String Authentication Windows Authentication and SQL Server authentication Windows Authentication only

So now you can see the big tradeoffs between the two systems, and why I said it's likely you'll need to use both. Central Management Servers should in theory be able to support all types of instances, but sadly, it just isn't implemented for some reason. My fingers are crossed that it's implemented in the next version.

 

Setup

Since the storage method of the two system is very different, there are very different concerns when it comes to setting them up. Really there isn't much to Registered Servers because of the fact that the list is isolated to a single user. With a Central Management Server, however, there are many more parts in play -- the price we have to pay for power and flexibility -- and so there are many more guidelines.

Briefly, the way I like to set things up is as follows:

  • Use Registered Servers for instances that I keep on my local machine (for development, or playing around) that aren't usually accessible from the rest of the network, and for SSAS/SSIS/SSRS because there's currently no other choice.
  • Set up a dedicated SQL Server instance just to be the Central Management Server instance, and register all publicly-available instances within a given domain. (One Central Management Server instance per domain is a good idea, particularly if that boundary separates development and production, for example.) The instance doesn't have to be powerful in terms of either CPU or memory, but it does need to be very reliable because we will be using it to manage our servers in good times and bad. Even Express Edition can be used to set up a Central Management Server, but for obvious reasons, you will want to take regular backups of at least the msdb database by some means.
  • I recommend installing a Central Management Server instance on a separate management machine (again, probably one per domain), and not on a production machine. (I wrote a bit about this in the comments on my answer here). Definitely do not register a Central Management Server on an existing production instance -- not only is that a bad practice in terms of separation of concerns for many reasons, but there's also a limitation that a Central Management Server instance cannot appear in the hierarchy within "itself" as would happen in this case.
  • (If necessary) Configure security to determine who can view, and who can edit a Central Management Server list. As eluded to in the table, this is accomplished by assigning membership to the msdb fixed database roles ServerGroupReaderRole and ServerGroupAdministratorRole (MSDN page) as appropriate. It may also be desirable to use multiple Central Management Server instances to separate tiers of registered servers, such as development, test/QA, and production, because there isn't any other way to control instance-level visibility besides the aforementioned roles.

 

Organizing the List

One of the great features of both Registered Servers and Central Management Server is that registered instances can appear in the hierarchy multiple times. Why is that great? Because it allows us to organize the instances in multiple ways within the same hierarchy.

We may want to group our instances together by:

  • Version and/or edition
  • Criticality (24/7, business working day, etc.)
  • Business area or department (development, test/QA, production, etc.)

And I'm sure you can think of a few others for your environment as well.

In fact, we may wish to categorize by more than one of these things for frequently-used parts of the hierarchy. When arranging the list for maximum usefulness, think about how to organize it such that if a single group is selected in the tree, changes would happen to all the instances within that group folder.

If you're setting this up for the first time, and assuming there aren't too many instances in play, it may just be a good idea to start with a relatively flat hierarchy, and build the groups as you find they're needed. The reason why this strategy isn't a good idea with many servers is because the ability to quickly move servers between groups in the UI is extremely limited. For a Central Management Server, the system tables in msdb could probably be edited directly, but I can't say I'd recommend that, and I haven't tried it myself either.

 

In a future post (likely a video demo), we'll dive into using and leveraging Registered Servers and Central Management Server to their full potential through the UI.

To leverage Central Management Servers externally, such as through PowerShell or an external application, we can run queries against the following two views in msdb: dbo.sysmanagement_shared_registered_servers and dbo.sysmanagement_shared_server_groups, or use the various stored procedures to manage the list.