Performance/ Management Data Warehouse Data Collector & AlwaysOn Availability Groups
This time, we are dealing with the „MDW“, short for Management Data Warehouse,( http://msdn.microsoft.com/en-us/library/bb677306.aspx), which I like to recommend as a minimal performance logging-approach.
From time to time, and most recently in the context of my PASS Essential „SQL Server Analysis tools & Techniques for Performance und general Monitoring“, the question arises as to whether the MDW operates together with the High Availability technologies Database Mirroring and AlwaysOn Availability Groups, and if so, how so.The short answer is: Yes, it does. The MDW operates both with Database Mirroring as well as with AlwaysOn Availability Groups.
The following graph illustrates a possible setup using the latter:
The server (0) holding the MDW database is located outside of the high availability nodes. The databases to be monitored are located in the AlwaysOn Availability Groups in the servers 1-3. Keeping the MDW highly available is not the objective. It is simply about being able to see the performance data of all databases, no matter in which server they are active at the moment.
Part 1: Databases in secondary role
If you set up the MDW as standard you will realize that after a failover, the data of the respective databases disappear from the “Disc Usage” reports of the server, while these were previously still present in the primary role.
The background to this is that after a failover, the respective databases now are present in a different server in the primary role, and now are no longer readable in the secondary, in the standard setting.
In this moment, the System Data Collection Set “Disc Usage”, or the underlying job “collection_set_1_noncached_collect_and_upload” cannot collect data for this database. In contrast on the new primary node these database will now reappear as long as they are active in the primary role there. In principle, this behavior is comprehensible: The Data Collector can no longer find any information on this database and assumes that the latter is no longer relevant – as if it was deleted. One may certainly wish for a possibility of intervention here; however, the MDW is currently not flexible in this regard.
The new report “Transaction Performance Analysis Overview” which is enriched through the newly existent “Transaction Performance Collection Set” in SQL Server 2014 also displays data for no longer active databases.
Having clarified this background, the possible solution is self-evident: The databases must remain readable.
With AlwaysOn High Availability Groups, this is in principal easily done:
However, one needs to be aware of the fact that these databases are now released for all reading access – which should be taken into consideration in respect to application architecture, performance as well as in terms of license. Hence, for the purpose of data collection for performance evaluation alone I CANNOT recommend it.
If however the business applications are supposed to maintain reading access to the secondary point anyway, the data collector is covered with this as well.
One more advice: The setting “Read-Intent only” unfortunately does not work with the MDW since one cannot manually adapt the Connection String accordingly. Database Mirroring does not support this option at all.
Part 2: Configuration of the MDW-Clients
Since the databases run on a different node after a failover, the MDW reports must be set up in all servers in which the Availability Group is running. Here, one needs to ensure that access to the central MDW-Server is possible from all servers.
To do this (before SQL Server 2014) the SQL Server Agent Account of the client-instance must be included in the mdw_writer role on the MDW-Server (mdw_admin is not necessary) when configuring the MDW through the “Configure Management Data Warehouse Wizard:
From SQL Server 2014, at the configuration of the data collection in the client, it is possible to provide a SQL Server Agent Proxy of the type “Operating System (CmdExec)” as account for the access to the central MDW-Server:
In this case, it is of course required to authorize the underlying Windows account in the server, instead of the agent itself, as „mdw-writer“.
As soon as all clients are authorized accordingly, one can read the data of all SQL Server AG nodes in the central management server. Depending on which server a database is currently present in in the primary role, it will then appear in the according subreport.
– This proceeding also works with mirrored databases in Database Mirroring scenarios – there one can only have one “partner instance” though.
Happy collecting
Andreas
Leave a Reply
Want to join the discussion?Feel free to contribute!