I recently had an opportunity to write an article for mssqltips.com on managing registered servers under “local server groups”, and Greg Robidoux was kind enough to do a review for me and he got back to me saying my script not working under SQL Server 2014 SSMS, though it works… Read more
As once a data architect, I cannot help but admiring and appreciating the data model behind the sql server job schedules. The core design is actually just one table, msdb.dbo.sysschedules, but the design beauty/elegance of the table is that it can support many different types of schedule with a rich… Read more
Recently I was working on a task that needs to handle various datetime calculations, and it is really fun.
Usually there are two types of approaches, one is to convert datetime value to string value and then through string calculations like substring, concatenation and convertion to get the result, another… Read more
After I read Greg Low’s post and my initial try, I kept on thinking that I need to come up with a solution that in theory is robust. The only way I can think of is to use a CLR stored procedure which will do the following three… Read more
I read with interests about Greg Low’s post “Reliably Dropping a Database in a T-SQL Script is Too Hard”, I think I have a solution, which at least I cannot prove it not working so far.
1: USE master;
4: IF EXISTS(
Now MS has decided to cancel all its advanced certifications. I am here to share some of my thoughts on this cancellation and my expectation for future.
I have read lots of blogs discussing this cancellation. Most are against the cancellation. Indeed, I agree this is not a decent… Read more
I first heard the word “agile <something>” about 10 years ago, I do not have profound experience to what “agile <something>” really is, but I do know that the key of “agile <something>” is a breakup of the traditional way of doing <something> and this agile way is coming…
In my last post, I discussed about the quantitative KPIs which, to me, are useful for “outsiders”, i.e. non-DBAs, to evaluate DBA work performance from business perspective. However, I believer there should be some qualitative KPIs that can be best used by “insiders” to evaluate the DBA work from… Read more
As a DBA, we may all have this question: How should my work be evaluated objectively? or in another way, what criteria would I hope to be used by others (my manager / clients / peers etc) to evaluate my work?
In DBA world, we love “no news is… Read more
According to Wiki, Procedural Justice (PJ) means:
Procedural justice is the idea of fairness in the processes that resolves disputes and allocates resources.
To me, this concept of PJ should be introduced to database administration domain as well, I’ll use the following example to illustrate the necessity.
As… Read more
Reading/scanning SQL Server Logs is a required DBA work item in all my work environments. I know there are lots different ways, mostly are using undocumented xp_readerrorlog or sp_readerrorlog. Even in PS way, this xp_readerrorlog is wrapped as a sql command in the PS script. Actually, with PS and Regular… Read more
SQL Server SMO is a great resource for DBAs, unfortunately, before PowerShell, it is a pretty deep learning curve to get familiar with SMO. The beautify of PS is its own discovery capacity. This is best described in the Keith Hill’s free ebook (http://rkeithhill.wordpress.com/2009/03/08/effective-windows-powershell-the-free-ebook/). So in short, we… Read more
One challenge in a super-multiple server (say a few hundred servers) environment is to know what sql services (SSRS/SSAS/SSIS/Engine/Agent etc) are installed and what their versions and the startup accounts are.
Case Scenario: (SQL Service Inventory)
We are managing hundreds of physical servers in our environment, most of the servers… Read more
One common surprise I have encountered in almost all my DBA environments is that a sql job that usually runs <x> minutes, sometimes can take <2x> minutes or even more time to finish.
Case Scenario: (Monitor / alert long running sql jobs)
We have hundreds of sql instances supporting many… Read more
As a DBA, sometimes we may need to demonstrate to some stakeholders when a sql server instance was last rebooted, i.e, just to prove the sql instance weekly/monthly availability . I know there are quite a few blogs / posts online talking about how to do this check, such as… Read more
As a DBA in a complex environment, we frequently need to backup various environment information. One of the backups is to script out the configurations / objects, for example, scripting out replication, jobs, policies and policy conditions etc. Almost all of these scripting work can be done via SSMS,… Read more
I have been blogged non-technical topics for too long and it is time for me to return to my favourite technical topics now.
I plan to write seven real-world cases where I feel PowerShell solution is much more efficient than pure T-SQL one, hopefully these cases can stir some… Read more
For the past 5 or 6 years, we have seen lots of innovative products emerged, from smartphone to tablets, from social media to cloud computing. However, I am somehow disappointed with Microsoft for its innovation speed on database administration front. Actually since SQL Server Management Studio (SSMS) was introduced… Read more
One thing lots of DBA teams, esp. the DBA managers, feel uncomfortable is that it is difficult to quantify/log DBA’s workload and as such it is hard to argue to the upper management for more justified budget and human resources. In worst scenario, this will cause the burn-out and…
I have to admit I am not a fan of the various 3rd party monitoring tools (after using a few of them in the past 10 years) mostly because of the following two reasons:
1. Lack of operation productivity. What I see from various products is just a common pattern:… Read more