January 14, 2013 at 10:47 am
A number of years ago, I implemented a remote query solution in SQL 2008 based off the remote execution code by Rudy Panigas shown at the link below.
http://www.sqlservercentral.com/scripts/Replication/31819/
This solution worked very well to help us remotely execute queries against 200+ SQL servers across 130 Active Directory forests or stand-alone workgroups. I am now looking to bring this solution forward to SQL 2012. When the script runs and tries to dynamically create a linked server connection, I get the following error message.
The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction.
In diving into the problem, I have confirmed that the while loop used to cycle through the cursor is the culprit. Does anyone know what changed between SQL 2008 and SQL 2012 that caused this to break? Does anyone know of any workarounds to allow this code to work again?
January 15, 2013 at 11:22 pm
I am not a huge fan of Linked Servers mainly because of quirks like this. You may not want to hear this because your process worked for years prior to trying to upgrade but have you considered giving the process an upgrade as well and porting it to PowerShell? And maybe implementing a Central Management Server (CMS) to help organize your instances? CMS and PowerShell have a nice alignment when it comes to multi-server management. Just a thought.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 16, 2013 at 5:21 am
We do have a CMS server today. There are two primary challenges with a CMS server. The first challenge is a CMS server only works with Windows authentication. We have a significant number of domain trusts (remember we managed 130+ AD Forests) that go a long way to resolving this hurdle. However, we have about 20 SQL servers that are not members of a domain and cannot be added to the CMS. The second challenge with the CMS is the inability to write code against it. Our primary process uses linked servers to centrally catalog SQL agent job failures and sends out a summary email of the failed jobs we need to review. Without the ability to issue a query against the CMS from within a SQL agent job, the CMS isn't functional enough for us to replace our current process.
With respect to powershell, we manage Windows 2000 to Windows 2012 servers and SQL 2000 to SQL 2012 with several MSDE/Express instances thrown in the mix. If we were only managing newer versions of SQL and Windows I think powershell has the potential to be a good option. Again, we can't orphan the 25 to 30 older SQL servers that we manage.
Knowing that the article I referenced was written in 2006, I certainly know we are using a bit of an old process. 🙂 I greatly appreciate the suggestions. If you have any other suggestions or workarounds for the limitations I noted, I would certainly appreciate the help.
January 16, 2013 at 8:23 am
That's the nice thing about using PowerShell to manage SQL Server. It leverages SQLDMO which works with SQL Server 2000 (SP3 I think) and above.
Regarding CMS, that's a bummer. I guess you could continue to maintain your list of servers the same way you do not, and just access that from PowerShell.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply