SQL Server 2008 R2 – Understanding PREEMPTIVE_REENLIST Wait Type

I had also previously assumed that this wasn't possible at all, until I started playing around with it.

From what I can tell, the matching is only done by the server network name that's entered.

If you attempt to add a server to the tree with the same network name, it won't allow it; however, if you use a different network name, even though it may point to the same instance, it does work.

For example, I successfully registered my local instance .\SQL2008R2DEV as a CMS, and then successfully registered (local)\SQL2008R2DEV in the tree. (Tested in 2012 Management Studio.)

From a management perspective, this may or may not be a good idea. Furthermore, if Microsoft decides to tighten down the policy, any reliance on this ability will force you to change your process.

Certainly if SQL1 would be a production instance in your case, this is a bad idea because you want to separate management duties from serving user data duties. I strongly recommend using a separate instance of SQL Server just for CMS purposes, particularly in a production environment.

I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes

Remote data modifications through a linked server use the sp_cursor model. The effect is similar to issuing 1000 separate single inserts (one for each row). If a round trip takes 250ms, 1000 such trips will take 4 minutes and 10 seconds. Using a bulk loading method such as bcp or SSIS will generally be more efficient (unless the number of rows to be inserted is tiny).

Another alternative for ad-hoc needs is to build a string containing a single INSERT statement with multiple rows in the VALUES clause. The statement is then used with EXECUTE AT, for example:

-- Note local-to-azure table name used, not four-part syntax
DECLARE @sql varchar(max) = 'INSERT dbo.Test VALUES ';

-- Build a list of 1000 numbers to insert    
SELECT @sql += '(' + CONVERT(varchar(11), n) + '),'
FROM dbo.Numbers AS N
WHERE n BETWEEN 1 AND 1000;

-- Remove trailing comma
SET @sql = LEFT(@sql, LEN(@sql) - 1);

-- For debugging
PRINT @sql;

-- Execute the finished statement at the remote server;
EXECUTE (@sql) AT AZURE;

The constructed insert statement executes at the Azure database, so a local name is used for the table (note: building a dynamic insert statement using a four-part target name buys you nothing).

Note that EXECUTE ... AT requires RPC and RPC OUT enabled in the linked server options.

The VALUES clause for a plan INSERT statement is limited to 1000 elements. There are ways around that (a VALUES clause in a CTE does not have that restriction). You could also choose to build 1000-row batches or build single-row insert statements, but if the there is that much data on a regular basis, you would probably be better off using one of the bulk loading methods instead.