Recently quite a few MVPs (here, here and here ) are handling out MSDN Ultimate subscriptions in the community. What interests me most is that most of them require the receivers will make a good use of the subscription, esp by contributing to the IT community. I cannot help saluting to their generosity and their dedications/affections to the community.
Database administration, as an important IT activity, has not caught its deserved attentions from both IT leaders and academic world. While on the other hand, in IT field, project management, software development and business analysis etc have been explored / researched extensively with continuous resources poured into their researches. So the development of database administration from both theory and practice perspectives mostly rely on grassroots movement, and I think those SQL Server MVPs are the representatives for such movement.
To be frank, I also want to have such a subscription so I can motivate myself to work on some interesting projects I have conceived for a while. Since most give-away owners require an introduction for the potential project with the subscription, I hereby give a brief introduction of what I mostly want to achieve with the MSDN subscription.
1. Protocol-based SQL Server Administration Framework: To be honest, I have the idea before I saw SQL Server 2008's policy-based management, I feel there are some overlaps between the policy-based admin framework and my proposed framework, so I’ll focus on some unique features / advantages.
A protocol will be defined in XML format, thus portable. A protocol can be defined as whatever a DBA wants. Most DBA administration logics / practices are embedded in various maintenance plans / monitoring jobs / SSIS packages, it is hard to document all these logics, and the adminstration practice which is a valuable asset can be lost easily due to staff change or time change. What I’d like to see is that a protocol is defined with at least three parts: the connection information; the process drivers (can be SPs, scripts, SSIS packages etc); the parameters. We can also add other sections, such as category key words, notes etc in the protocol xml file.
The advantage of this framework is that the knowledge of database administration practice is in a good shape (i.e. documented in the first place, and processable / queryable via rich processing capability to XML document) to be portable and “inherited” as a corporate or individual asset.
The deliverable will be samples of protocols and the real-world use cases.
2. Test Case Library System and Methodology
I always dream to set up a test case library that I can use to watch and verify various SQL Server behaviours. A very simple example, under which scenario, it is better to use temp table than to use table variable. Another example, under what condition, sql server will use hash join instead of nested loop join. I need the empirical and quantified data, which can only be achieved via repeatable test cases.
In my mind, the test case library should include the following features:
(1) Individual modules that can impact various SQL Server performance related properties, for example, I can have a module that generates enough memory size inside the plan cache so that sql server has to clean up plan cache, which may impact the performance of other normal sql executions.
(2) Automatic test case analysis feature. Once I set up the test, I want to automatically log and analyze the test process from the beginning to the end. This may include wait type, performance counters, execution plans, aggregate physical / logical operators used, and compilations / recompilations etc.
The deliverables will be a set of scripts and data models to create the test case library and a document about the best practice for using the test case library. I believe this is very suitable for open source as more people add testing scripts to the library and add usages of the test cases, we can help DBA community to test various sql server features and thus better understand SQL Server performance behaviour.
I have some other projects in my mind, one of which is to “make” a monitoring server as a twitter-enabled machine that can twitter me what my production servers are doing from time to time.
I hope my proposed projects can raise the interests of some same-minded MVPs and fund me with a MSDN subscription and I will sing your praise in every step of my project.