Archive
Updating Central Management Server (CMS) Register Server List Automatically
Microsoft released Central Management Server (CMS) with SQL Server 2008; it allowed us to have great functionality for multi-server management. However keeping that list up-to-date can be time consuming and tedious. For multiple reasons, primary reason because we are only DBAs, so we don’t get told anything. We are expected to just know it right!?!
Okay, I will not get into rant about that topic. Anyhow SQL Server instances get installed in organization (without DBA knowledge) or get uninstalled (again without DBA knowledge). But you have the servers in your CMS registered server list; or would like to get those new servers in that list. So how can we go about it?
There is no easy way to discover all instances in your domain; one of the few methods are:
- SCOM
- SCCM
- MAP Toolkit
Probably few other, which I don’t know about. But point is there is nothing Native to SQL Server. So when one of my clients asked “How can we update CMS automatically?” Only thing I could think of is SCOM. So here it is, a PowerShell solution, that updates CMS from SCOM.
If your interested in trying it out please download a copy from Copeplex, here. It is easy to setup, the documentation on Codeplex site includes instructions.
If you use it, please leave feedback :).
Calculating Datetime Based on NT Time
A colleague of mine gave me an interesting challenge today. I am by no means a T-SQL expert, however it was interesting dissecting the problem.
Give the time, 128271382742968750, what does it mean? How to read this?
We can use command line utility called w32tm.exe with following command to get the exact time…
w32tm.exe /ntte 128271382742968750
Return we get
148462 05:57:54.2968750 – 6/24/2007 8:57:54 AM (local time).
Problem SOLVED!
Well not quite, this doesn’t translate well against many gigs of data that my friend wanted to translate. So reading the KB555936, started breaking down the time-stamp above.
- Multiple 128271382742968750 by 100 to get 12827138274296875000; because the time is recorded in number of 100 ns have ticked by since January 1, 1601.
- Next divide 12827138274296875000 by 1,000,000,000 to get number of seconds passed since January 1, 1601. We get 12,827,138,274.2968750.
- We can ignore everything after the decimal, that is number of ms passed (which we don’t care about).
- Unfortunately we cannot use the DATEADD function in SQL Server to calculate the date, as in SQL Server we can go back to only 1/1/1753. So we need to calculate the number of seconds passed from 1/1/1601 to 1/1/1753 and subtract that from that.
- And that is 4,796,668,800 seconds (you can take my word for it, or calculate it using PowerShell script, below).
- So we take the number calculated in step 3 and subtract 4,796,668,800 from it. To get 8,030,469,474 seconds passed since 1/1/1753. Now we can use our ADDDATE!!! Yeeh? Right?
- Umm unfortunately NO. The DATEADD function accepts a integer parameter, and that number is too big so we get row over flow error :(.
- So we have to do some additional math, we take that number and divide by 60, to get number of minutes passed. We get 133,841,157.90.
- Now the .90 is important as we’ll need to calculate the seconds; so don’t forget it. But we can now pass in the above value to get the date.
- SELECT DATEADD(Minute,133,841,157.90,’1753/1/1′); almost done. DATEADD function truncates any decimal value so we do not get the number of seconds passed.
- So now we have to add the number of seconds to the puzzle. We can do that using SELECT DATEADD(Second,.90*60,DATEADD(Minute,133841157.90,’1753/1/1′)).
- Now we have our final answer of 2007-06-24 05:57:54 :).
- Just for heck of it if we wanted ms also, the answer should be
SELECT DATEADD(MILLISECOND,0.296800000*1000000,DATEADD(Second,.90*60,DATEADD(Minute,133841157.90,’1753/1/1′))).
So there you have it, NT time in normal time using T-SQL :). Lots of work, but possible heh.
SQL Server Script to Calculate the NT Time in Readable formatting using T-SQL, combining all 12 steps into single step:
DECLARE @NTTime BIGINT DECLARE @TimeSkip BIGINT DECLARE @BaseTime DATETIME SET @NTTime = 128271382742968750 SET @TimeSkip = 47966688000000000 SET @BaseTime = '1753/1/1 0:00:00.000' SELECT DATEADD(SECOND,((((@NTTime - @TimeSkip)*1.0)/600000000)-ROUND(((@NTTime - @TimeSkip)/600000000),0,1))*60,DATEADD(MINUTE,((@NTTime - @TimeSkip)/600000000),@BaseTime)) AS NormalTime
PowerShell Script to find time passed between 1/1/1601 and 1/1/1753:
[DateTime]$LowDateRange = '1/1/1601' [DateTime]$HighDateRange = '1/1/1753' $HighDateRange.Subtract($LowDateRange)
