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
As a DBA, when can you consider yourself as a senior DBA?
If you read online posts, you will find most of the time, a senior DBA is judged by “Do you know this and do you know that?” approach, which to me is subjective and may cause more…
As a DBA, we have to take various actions based on huge amount of information received. In one perspective, our productivity is limited by our capability bandwidth to process the information received. However, if this information is frequently “polluted or even poisoned”, our productivity is surely compromised.
I define “polluted… Read more
I define DBA operation productivity as the total positive values brought to the work environment by a DBA in a unit of time.
How to improve productivity is really a big topic, but every big task can /should start from the small things. So here I’d like to discuss… Read more
Just as a society’s civilization is mostly nurtured by its culture, I believe that DBA work quality is mostly determined by the requirements from management. I had been fortunate to work with a few excellent managers in the past and benefited a lot from their requirements. I’ll share some… Read more
For the past few years, I have seen tens of sql server boxes added in my work environment every year, meaning more complexity and overhead cost in database administration, I gradually come to a “green” design principle with two points.
1. designing administration framework with a “footprint” as small as… Read more
As IT professionals, we all will consider and take actions, at some point of time, to “invest” in our careers. So after I read the following interesting article a while ago, “8 Ideal Jobs for M.B.A Grads”, I think it is a good topic for a blog.
In the… Read more
I once talked about the DBA team building about 3 years ago, now when I look back, I still believe my original idea is a good start, however, I feel it is not enough, so I’d like to explore some other areas for DBA team building, that is how to… Read more
I once wrote a post to find the largest tables in a database, and now after studying PowerShell, I feel it can be written in a more concise and efficient way.
The following script is to find the largest 3 tables in row count in AdventureWorks
$srv = Get-SQLServer –sqlserver… Read more
I feel somehow sad today (Aug 31, 2011) to see one of my colleagues “laid-off”. I put quotation mark around “laid-off” because he is a contractor (same as myself), so it is not, strictly speaking, the real laid-off, however, this “laid-off” was a bit unexpected as the project we are… Read more
As a DBA, we have a lot to work on and also we have a lot to learn about. The asset that makes us as a DBA is our professional knowledge and skills, and as such, from time to time, we need to review our knowledge/skill portfolio and how we… Read more
One of interesting tasks I once had is to check the # of code lines for all user stored procedures (SPs) in a user database.
I once developed a pure yet lengthy t-sql solution by counting the new line ASCII code (i.e. char(0×13) + char(0×10) ).
However, with PowerShell and… Read more