Sql-server – Separating system databases from MDW SQL Server 2008 R2

management-data-warehousesql serversql-server-2008-r2

We have the Management Datawarehouse feature configure on two of our SQL Servers. Both point to the same central database and upload their results to it.

Everything works fine with the built in reports run from the instance. However, I am taking all the data from the snapshots.diskusage table to help show how our databases have grown and formulate a storage solution for the next chunk of time. For the majority our databases are uniquely named so there is no concern there running:

SELECT * 
FROM [RKYV_MDW].[snapshots].[disk_usage]
WHERE database_name = 'tempdb'

The tricky part comes with the system databases, of particular annoyance is tempdb. Seeing as both instances have a tempdb and upload their stats I have double the number of data points and the graphs turn out somewhat meaningless. I can't see anything in the table bar snapshot_id that might help me seperate the two out but I am unsure where this would be unique to a particular instances tempdb.

Does anyone have any thoughts about this? My alternative is to select every other line (from a quick ganders at the data) but feel there must be a more elegant way out there.

Best Answer

Join via snapshots_internal to source_info_internal which has the server instance name, eg

SELECT du.*, sii.instance_name
FROM [snapshots].[disk_usage] du
    INNER JOIN core.[snapshots_internal] si ON du.snapshot_id = si.snapshot_id
        INNER JOIN [core].[source_info_internal] sii ON si.source_id = sii.source_id
WHERE du.database_name = 'tempdb'

Related Solutions

Sql-server – Why does ‘sysfiles1’ show up in the sys.objects catalog view in SQL Server 2008

sysfiles1 is an internal catalog table. There are many of them (basically any table with object_id between 1 and 99 is, by definition, an internal catalog table). The query compiler refuses to bind queries to them under normal context, so if you try to select from them you get the error you see. DAC connections can bind to them and retrieve data, but you still won't be able to update them.

This is all described on MSDN, see System Base Tables.

sysfiles1 though is a special case, not documented in the link above. ~~This catalog table exists only in databases upgraded from SQL 2000~~. See http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/09/19/761437.aspx:

... sysfiles1 table is a linked-heap. This table contains the locations of the files comprising the database ...

Sql-server – Running different instances of SQL Server from different networks on one box

If you can access the instances at the IP layer then you will always be able to connect using either SQL Authentication (if enabled) and even Windows Authentication using the runas /netonly trick. Say you are in the domain 'foo' and you want to connect to an instance in the domain 'bar', and there is no trust relationship between 'foo' and 'bar'. Since the instance in the 'bar' domain can only authenticate an user in the 'bar' domain you would normally have to be running as 'bar\user' to connect to this instance, but of course your host in 'foo' will not let you run as 'bar\user'. runas /netonly to the rescue: start your client process (eg. SSMS) using the /netonly option:

runas /netonly bar\user "c:\program files\...\ssms.exe"

When launched this way (you'll be prompted for bar\user password) the SSMS will authenticate to any resource over the network as 'bar\user' and you'll succeed to connect to the 'bar' based SQL Server instance. This trick works with local accounts as well, so instead of bar\user you can use <machinename>\Administrator to appear to that machine as the local administrator.

If the destination server is unreachable at the IP layer then you have no option, you must make it reachable, either by VPN, or by DirectAccess, even a netcat bridge can work with the /netonly trick.

Related Question