Are you tired of being the one to arbitrate arguments -- er, discussions -- about which copy of a database is authoritative? Spending too much time restoring data from backups after someone makes unauthorized changes? Too often, database administrators (DBAs)
It’s one of the important new features in SQL Server 2008 R2. MDS is a master data management component that hands over data custodian duties to the people who own the data. The idea behind it goes something like this: In most organizations, there are all kinds of data sources. Official data is stored in databases, while less-formal data lives in spreadsheets that employees are working with. Invariably, some of this information overlaps, and that overlap often creates conflict.
So the reason behind those contentious conference-room discussions about data is this: Different people are working from different sets of data. Some sets may be out of date; some may be incomplete; others may be flat-out incorrect. And those bad data sets often lead to bad business decisions.
MDS is designed to help prevent all that by maintaining a single, authenticated, authoritative data set for various sources. Your organization decides what data goes under MDS: Perhaps it's historical sales data or a subset of your customer demographic information. Maybe it’s data that many people will need to access and or data that will be used to make business decisions. All that can be moved under MDS.
MDS will let any employee see the data (assuming you've given her permission), but it controls who can modify the data. It also maintains a version history of changes to the data, enabling improper changes to be "undone" or "rolled back." The MDS-controlled data thus becomes a "single version of the truth," getting decision makers on the same page. It also helps alleviate the need to turn to backups, since the data owner or custodian can initiate rollbacks without you.
The good news is that Microsoft MDS doesn't require a great deal more work from you. Once you've configured the feature (a simple enough process that's well-documented in SQL Server Books Online), designate a manager, or custodian, for the data. That's not necessarily someone in IT; instead, it's the businessperson who is responsible for that data and its accuracy.
From that point, the custodian can use a Web-based interface to manage the data. She can do things like assign permissions to the data, monitor changes to the data and even receive alerts when changes are made. She can roll back changes, too, if necessary. It takes you, the DBA, a bit out of the loop -- which is perfect!
MDS does require a bit of extra planning. For example, someone will have to identify which data should be placed under MDS control. The product also has its own data, which it uses to track permissions and versions, and that data will require space. SQL Server Books Online provides some guidance on how much space you can expect MDS to use; in general, its overhead is pretty small until you start making changes to the data that require MDS to save old versions for potential rollback. MDS also provides a Web application, which means that SQL Server hosting MDS will have some additional security considerations, such as additional open firewall ports.
Above all, however, Microsoft MDS is relatively easy to install, and it makes it easier for businesspeople to manage "their" data. And that, reluctant DBA, is easier for you.
Miss a tip? Check out the archive page for Don's series, SQL Server for the Reluctant DBA.
ABOUT THE AUTHOR
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 books on IT and a speaker at technical conferences worldwide. He can be reached through his website at www.ConcentratedTech.com.
This was first published in September 2011