OTLT Put to Bed
Yesterday we reviews some of the developer issues resulting in the use of OTLT (One True Lookup Table). Today I just want to talk briefly about the opportunity available for a tool to manage the contents and distribution of Lookup Tables.
There are number of tools today doing a great job of comparing database table contents, and generating scripts for updating another database with the changes. In order to use these tools you must have the contents of both databases. Some comparison tools will work directly from a database backup without even restoring the contents.
The neat thing about these tools is the ability to compare different data sources which may be from different persistence platforms. As long as you can make an ODBC connection to the data you can compare contents of tables.
That defines one answer, and exposes the problem. Here you really need something like Master Data Services with a twist. I think we need a tool that allows us to define different database instances (internal or external) and to associate data from a master repository to each instance.
This tool would be responsible to track what the lookup table contents are for any instance, and release updates to those instances through different communication methods to an instance through either a push or pull model.
That’s my brainstorm for the day. With a tool of this nature it could be used for development purposes to migrate data from a Developer Sandbox to A Development Environment, QA, or even a Production Database. Script out changes and you could associate them in Version Control with the BITS used to build an Instance or your product.
Do you want to polish or improve on the brainstorm? Drop me a note at btaylor@sswug.org.
SelecTViews
Hosted by Stephen Wynkoop
Open source databases as a tool for expanding your systems, Interview with EnterpriseDB CEO, Ed Boyajian, a SQL Server tip of the day…Also, information about tablets, Jeopardy! challenge, new Gartner BI report and much more.
[Watch the Show]
$$SWYNK$$
Featured Article(s)
Top Ten Database Blunders (Part 3 of 4)
As a PTO (Performance, Tuning, and Optimization) consultant, I’ve seen some pretty crazy problems. Although the names have been changed, these stories are true. You’ll hear me rank the 10 worst real-life blunders, why they’re so expensive, and how to avoid them in your career.
Featured White Paper(s)
Upgrading to Microsoft SQL Server 2008 R2 from Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000
More than ever, organizations rely on data storage and analysis for business operations. Companies need the ability to deploy… (read more)
Featured Script
dba3_FileGroup_Names_of_DB_Objects_demo
demo of how to identify where objects may reside… (read more)
Unfinished Business
Tracy asks: "I’ve been enjoying your articles, particularly on the characteristics of a DBA. Unfortunately I missed a few of them and only got to read the first one. Is there a way I can access the articles I missed?"
Answer: If you go to www.sswug.org there is a link at the very bottom of the page for Editorials. Click on that link and you are presented with recent editorials. Enter search criteria up top to restrict the content and the search. Your results are filtered by your criteria, and only display items from editorials.
Robert writes: "I am writing you regarding SSWUG.ORG newsletter on February 19th titled “Database Controls – History Tables (Feb. 9, 2011) (805578)”. I have a question about the following comment in the newsletter (from David):
At my company, we do something (unique?) in our system to cover ourselves: we have a trigger on every table, that writes every change to every record out to a mirror table in a separate database with the date/time of the change and the type of change (insert/update/delete).
I am analyzing a way to audit my database. I need to know what has changed after an insert/update/delete. We are maintaining data, and when a change is made, that change must be validated by another user. If logged what the change was, front end development could then highlight the changes on the screen.
Currently we only track the changes within several stored procedures that handled batch imports of data. When an insert/update/delete is made, the primary key of the modified table and the change is logged. This type of auditing is not consistent and also does not cover any back end changes or changes on the edit screen.
Do you have any examples of the trigger David is referencing or do you know of any articles that discuss this type of auditing? Do you know any additional details about David’s “Mirror” database? More information on this subject "
Answer: I am in the process of preparing the script from David. You will be able to find it on the www.sswug.org site where scripts are stored. In the meantime, there are two scripts already uploaded you can find on the site. Click on the Scripts tab. Then in the search box enter Audit Trigger. Also, David’s company has submitted a copy of their trigger generator which will be updated ASAP. You should be able to find it in scripts searching on Audit Trigger in the next week or so.
If you have any other questions or suggestions please drop me a mail at btaylor@sswug.org. You can also connect with us on Facebook or twitter.
Cheers,
Ben