Monthly Archives: July 2013

Amazon Partners With Cloud vLab To Teach Developers How To Use AWS


Using MSX and TSX to scale out your maintenance plans

For our front office alone, I manage about 15-20 SQL database servers.  Add to that other database servers that I coordinate with other sister companies under umbrella company, I say, total development database servers would run up to total of 25-30 servers.

You would ask, how do I manage the scheduled jobs and server maintenance plans? Easy. Or should I say SQL Server made this task easy for thousands of DBAs out there. I use the MSX and TSX feature in SQL Agent. I was very happy that this feature came out in SQL Server 2008. I am not sure though how far back it came out. I just started using it in SQL Server 2008.

The MSX and TSX, or sometimes called also as master and target servers, is surprisingly also easy to setup. You will just need an additional SQL database server to serve as your Master server. As the name implies, this Master server will contain all the scheduled jobs and maintenance plans that you need scale out to your Target servers. The Target server maybe tens or more SQL database servers that will be targeted by the Master server to run the jobs.


As general rule, you should setup a service account for SQL Agent in your servers and it would be great if you can use a single account across all servers for the SQL Agent service. Then you have to manage permission for just a single account. As a good practice make sure also that this account will be granted the least previleges required for the service to run.

Another thing that you will need to do is to bypass the level of security between the master/target communication during validation of the MSX server. On the server that you want to enlist as a target server, run regedit to open registry editor. Then go to expand HKEY_LOCAL_MACHINE > expand SOFTWARE > expand Microsoft >  expand Microsoft SQL Server > expand MSSQL10_50.MSSQLSERVER (It means i’m running SQL Server 2008 R2 and installed MSSQLSERVER instance, it could be different in your case)  > expand SQLServerAgent. On the right pane for the values, look for MsxEncryptChannelOptions and right click. Modify and change the value to 0. Usually this value is set default 2. The value of 0 means we are disabling this encryption on the TSX.


For more information on Encryption Options in the target servers, click here.

Creating a MSX Server

To make a database server the Master or the MSX server, go to SQL Agent right click on Multi Server Administration and click on Make this Master. This will launch the wizard as below,

Master Server Wizard

Enter the email for alerts to the MSX operator, enter the target servers you want to be enlisted in this MSX server. After the wizard is completed and it is successful, you should be able to see (MSX) beside your SQL Agent, and from your Jobs menu, there is menu for Local Jobs and Multi-Server  Jobs.

Enlisting Target Servers

Now that we have prepared a server as our Master server, next we start adding target servers to this Master. To enlist a database server to the MSX server, right click on the SQL Agent> Multi Server Administration > Make this a Target. This will launch the wizard for enlisting the server. See below screen shot of the wizard.


Click Next, enter your MSX Server, SQL Server will check compatibility then if it passes then you’re good to go.


Once you are successful in enlisting your database to target servers, refresh the SQL Agent menu in your SSMS. You should be able to see beside it this (TSX: <MSX servername here>) noting that the server is now enlisted. See below.

Verify success

Scaling Out Your Maintenance Plans

As we have successfully setup a MSX and TSX servers, we are now ready to create maintenance plans in the MSX server and scale this out to the TSX servers.

To start scaling out your maintenance plans to your target servers, create a maintenance plan in your MSX server. Then  on the part of the editor window click on ‘Servers’, see similar screen below

Adding Target Server

Then add all your target servers you wish this maintenance plan will run. Save the maintenance plan. On your target server, refresh the SQL Agent and go to Jobs menu. The scheduled jobs created from the MSX server should appear in your TSX server jobs with (Multi-Server) note beside it. See below.

Jobs Listing

All maintenance plans from the MSX server cannot be updated or deleted in TSX server.

If you wish to remove the target server from being enlisted, right click on the Multi Server Administration under the SQL Agent menu and click on ‘Defect’. You will need to confirm that you are sure you want to defect this target server.

Confirm Defect

Click Yes to confirm and refresh the SQL Agent in your target server.

Every time there is a new database server, I just enlist this new server, go to MSX server update the maintenance plans to scale out my maintenance plans to this new server and done!  To make this more effective in your database administration, I suggest you add email notifications for any failed maintenance plans or other alerts in your MSX server.

How secure is your IT career? Really?

I was reading some articles from my favorite influencers in LinkedIn and I came across this one. I was enlightened actually so I wanted to share it here in my blog site. It made me rethink and evaluate my career plans and where I want it to be in 5 years or beyond that. Being just an an expert in database server administration should not be enough and OK for now. I want to be upfront, the leader of the pack. If I was in group of job seekers sitting in a room for interview, I want to be the favored one. This article is good news for me, so while enjoying my coffee and some free time in the morning (development team not noisy yet), I’m going to proceed with the rest of the article.

A good read, really.


SQL Server Management Studio Rainbow Edition

Nice point on the DBA being PROACTIVE. It’s just how the world should be.

The SQL Pro

Ever wanted to know the reason behind the Database color scheme in SQL Server Management Studio?

I have always wondered why all the Databases in management studio were Yellow (Online), Grey (Read-Only), Lighter Yellow with text next to it (something like Restoring), or Yellow with a Red Icon (Offline). Have you ever wondered why there aren’t any more useful colors like Red for when a Transaction Log is Full or Purple when your Statistics are Outdated. I have a theory on this that I have entitled “The Theory that you have to actually work to call yourself a DBA.”

colorful databases

View original post 1,236 more words

What I’m sayin’ is…everything is fine.

Let’s say you are a member of the development team. Your team got a new project in the works, so everyone’s excited and and prepping for design and coordinating with other team via lots of project and update meetings for the last few weeks. Everything about the project requirement is already documented, including your requirement for a database server in DEV, UAT and Production environment.

Thanks to YamamotutetsuyaSo the DBA reports back and she says, “Everything is fine”. She shares some technical document she prepared about the database server you can use for your project with your team. And you’re like “OK as long as we have a database and we can proceed now”. Do you dare ask “What is that?” and “Whats the backup plan”? and “How secure is this blah blah blah” or do you just accept and let the issues clam up during your development phase?

In any applications development project, it is important that the DBA and the development team discuss the project specifications and database server requirements. This is to avoid wasting time debugging your stored procedures and why you cannot store Chinese characters in your database when should be able to. It is important that the development DBA is made part of the project development team.

The following terms maybe used by your DBA in her technical document and here’s why you should understand what it is about.

1. SQL Server Version or Edition – This is the edition of the SQL Server that you will be using and working on during your development, your UAT or testing and deployment or when your project goes live. Ideally, the SQL Server edition used should be the same across all platforms to eliminate edition and compatibility issues once your project goes live.

2.  Logical Drives – You need to know how large is the disk space size. There are times during development and testing that you will come across errors of no more disk space. You need to make sure that capacity planning for IO, and how fast your data will grow for the next 3-5 years is discussed with your DBA.

3. Security – You need to know how the DBA set this up. If you have SQL login accounts that you need for your application then the DBA should set this accordingly and allow SQL login to connect to your database. If the database server has the security settings set to Windows Account Authentication only, then your SQL logins won’t be able to connect to your application.

4. Backup Strategy and Disaster Recovery Plan – This is self explanatory. All database servers must have a backup plan and disaster recovery plan in the event of unplanned downtime. You know as they say, “expect the unexpected”.

5. SQL Server Collation Settings – The SQL Server collation settings determines how the SQL Server will store your data and sorting and when you need to compare value strings. This is also important in your application when you need to store Katakana or Chinese characters in your data. You need to check with your DBA whether you can store these types of characters or if you need your application search engine to be case insensitive or not and other similar scenarios.

6. Connectivity – I mean connection strings. How is your application going to connect to the database? What is the IP address or Host/Server name? Is it via OLEDB or ODBC connections? The default port setting for SQL Server is 1433. Has this been changed to some other value?

There are other numerous settings in SQL Server that you will need to discuss with your DBA. The most important thing is that you specify clearly your requirements and that everything is laid out in the table. Then document these discussions so that expectations and responses are managed.

Then everything will be fine.

Image courtesy of InfusionSoft.


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali -

Meels on Wheels

Meels for breakfast, lunch and dinner.

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment