Modifications

May 27, 2009 at 7:41 PM
Edited May 27, 2009 at 7:41 PM

 

A couple of things as I dug deeper today

In the servers.disks table I believe the servername column should be a varchar not a varbinary   , [ServerName] [VARBINARY](150) NOT NULL

Also I decided to add key relationships between the tables adding a new column to the  database, sqlserver and disk tables referring to the systemkey in the system table. - I am also trying to consolidate my entries to match the names in the MSDB. Unfortunately the MAPS database does not present instance names the same way that the msdb.dbo.sysmanagement_shared_registered_servers_internal does i.e SERVERNAME\INSTANCENAME. so that may be somewhat of a manual process

I did write some queries to import data that I’d be happy to share although I did not populate all the columns.

I would like to be consistent between all tables and be able to call the powershell scripts from one place

Coordinator
May 27, 2009 at 10:35 PM

Agreed. The VARBINARY is to stay compatible with the types of columns in the joining tables. The Primary Key is not the way I would like to go – I like your idea of a surrogate key, but unfortunately the Policy tables, MDW tables and EPM tables all use the server name as the key. To stay consistent with those, I chose the natural key. The other reason I went this route was that there will only ever be a “few” servers – meaning less than a thousand or so – so the risk of corruption in the keys is low.

But definitely change your design to match your specifications. And you can read my logic in the article I reference on the first page of the project.

Thanks for the work!

Buck Woody

SQL Server Technical Specialist - Microsoft

(425) 707-4863

http://blogs.msdn.com/buckwoody

http://buckwoody.com

https://academymobile.microsoft.com/pages/podcasterdetail.aspx?aid=739 

No trees were killed in the sending of this message, but a large number of electrons were terribly inconvenienced.

From: iain27 [mailto:notifications@codeplex.com]
Sent: Wednesday, May 27, 2009 12:41 PM
To: woodyb@hotmail.com
Subject: Modifications [SQLCMS:57648]

A couple of things as I dug deeper today

In the servers.disks table I believe the servername column should be a varchar not a varbinary , [ServerName] [VARBINARY](150) NOT NULL

Also I decided to add key relationships between the tables adding a new column to the database, sqlserver and disk tables referring to the systemkey in the system table. - I am also trying to consolidate my entries to match the names in the MSDB. Unfortunately the MAPS database does not present instance names the same way that the msdb.dbo.sysmanagement_shared_registered_servers_internal does i.e SERVERNAME\INSTANCENAME. so that may be somewhat of a manual process

I did write some queries to import data that I’d be happy to share although I did not populate all the columns.

I would like to be consistent between all tables and be able to call the powershell scripts from one place

Read the full discussion online.

To add a post to this discussion, reply to this email (SQLCMS@discussions.codeplex.com)

To start a new discussion for this project, email SQLCMS@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com