The Multifunctioning DBA


October 21, 2008  11:32 PM

Update Server DNS Search Order using Windows Powershell



Posted by: Colin Smith
Microsoft Windows, Powershell

Recently my company had a DR Test. This is a test we do a couple times a year to make sure that in case of a disaster we would still be able to put out a product. During this test we found the DNS Search Order for some of our Windows Servers was not set up to be as efficient as possible. We also decided to put a second Domain Controller at our DR site. This means that we also need to add an IP to the Servers DNS Search Order.

I thought that this would be a good opportunity to do some Powershell and get this done quick and easy. Since I know Powershell can use WMI I thought that would be a good way to attack the issue. First I needed to get a list of the servers that needed the update. In order to do this I was able to extract data from a database and put it into a CSV file. The list that I obtained, however, was not a list of only the Windows Servers at the DR location but a list of all of the company’s servers in all of our locations. That is OK though; I will just have to use Powershell to sort the list for me and only modify the DNS Search Order on servers that need to have the change made. To do this all I had to do was take advantage of the power of the import-csv comandlet. Here is what I did:

$serverlist
=
Import-Csv
“D:\Scripts\Powershell\DNS Update\Data\newserverlist.csv”

$downservers
=
“D:\Scripts\Powershell\DNS Update\Data\downserver.txt”

echo
“name, status, facility, PrSysAdmin, OS” > $downservers

foreach ($line
in
$serverlist)

{

    $name
=
$line.DevName

    $status
=
$line.status

    $facility
=
$line.Facility

    $PrSysAdmin
=
$line.PrSysAdmin

    $os
=
$line.OS

    $nic
=
$null


    ####################################################################################

    ## Sort list looking for all Active Windows Servers in the Deer Valley Datacenter ##

    ####################################################################################


    if (($status
-eq
“Active”) -and ($os
-eq
“Windows”) -and ($facility
-eq
“Deer Valley”))

    {

        ########################################################################################

        ## Ping Every Server that is in this list to verify it is reachable. If not make note ##

        ########################################################################################

        $up
=
Get-WmiObject
-Class
Win32_PingStatus
-Filter
“address=’$name’”

        $ping
=
$up.StatusCode

        echo
“`n$name is $ping`n”

        if ($ping
-eq 0)

        {

            Echo
“If server is pingable I will then get the adapters on the server and rule out the -Bak interface by IP. I will then update the DNS Search order.”

            $nic
=
Get-WmiObject
Win32_networkAdapterConfiguration
-ComputerName
$name
|
where-Object{$_.IPEnabled
-eq
“True”} |
where-Object{$_.IPAddress
-notlike
“169.*.*.*”} |
where-Object{$_.IPAddress
-notlike
“192.*.*.*”} |
where-object {$_.DHCPEnabled
-ne
“True”} |
where-Object{$_.IPAddress
-ne
“0.0.0.0″} |
where-Object{$_.IPAddress
-notlike
“10.111.25.*”} |
where-Object{$_.IPAddress
-notlike
“10.111.24.*”}

            if ($nic
-eq
$null)

            {

                $nic
=
Get-WmiObject
Win32_networkAdapterConfiguration
-ComputerName
$name
|
where-Object{$_.IPEnabled
-eq
“True”} |
where-Object{$_.IPAddress
-notlike
“169.*.*.*”} |
where-Object{$_.IPAddress
-notlike
“192.*.*.*”} |
where-object {$_.DHCPEnabled
-eq
“True”} |
where-Object{$_.IPAddress
-ne
“0.0.0.0″} |
where-Object{$_.IPAddress
-notlike
“10.111.25.*”} |
where-Object{$_.IPAddress
-notlike
“10.111.24.*”}

                $ip
=
$nic.IPAddress

                Echo
“$name has the following $ip but is set up for DHCP so this script will not modify this server” >> “D:\Scripts\Powershell\DNS Update\Data\iplist.txt”

            }

            Echo
“`n$nic will be modified on $name to the new search order”

            $ip
=
$nic.IPAddress

            echo
“$name has the following $ip” >> “D:\Scripts\Powershell\DNS Update\Data\iplist.txt”

        }

        else

        {

            #Echo “$name is down and sysadmin to notify is $PrSysAdmin”

            Echo
“$name, $status, $facility, $PrSysAdmin, $OS” >> $downservers

        }

    }

}

So this looks like a lot of code but it is not really too bad. First I am getting the list of my servers into a variable so that I can start to determine what servers are of interest to me and need to be changed. Once I have determined what servers I need to update, I then attempt to ping the server to verify that it is available for me to make the change. Some of the servers may be in a DMZ and some may just be obsolete and no longer in use. (Depends on if the administrators update the database.) If I am able to ping the server then I connect to WMI and I sort through the NICs that are on the server and determine what one I need to change. The standard for us is that one will be on our main network and has a static IP. Because of this I can determine what needs to be changed by sorting on those requirements. If a server has more then one NIC that meets that criteria then I do not want this script to change it. I want the servers administrator to take a look at it to verify that it has been set up correctly. In the above script I am just outputting to a file a list of all the servers that will be updated and a list that will not. When I actually make the change I will put in the following code in order to do the update:


$DNSServers
=
“10.111.1.36″,“10.97.65.23″,“10.111.1.88″,“10.111.1.19″

$nic
=
Get-WmiObject
Win32_networkAdapterConfiguration
-ComputerName
$servername
|
where-Object{$_.IPAddress
-notlike
“10.111.25.*”} |
where-Object{$_.IPEnabled
-eq
“True”}

$nic

foreach ($adapter
in
$nic)

{

    $adapter

    #$adapter.setDNSServerSearchOrder($DNSServers)

}

foreach ($adapter
in
$nic)

{


$adapter.setDNSServerSearchOrder($DNSServers)

}


This code will do Update the DNS Search Order with the values defined in the $DNSServers variable.

I hope that you have found this post useful and if you have any questions please let me know. Also you can check out my website at sysadminsmith.com for help with just about anything tech and see other blog posts as well.

October 21, 2008  8:54 PM

Modify Perfstat Statspack Job in Oracle 920



Posted by: Colin Smith
Database, DBA, Oracle

Today I was tasked to change the statspack job to get a snapshot every 15 minutes instead of once every hour. These statspack snapshots are something that Oracle does in order for a DBA to look at historical performance data. The idea in making this change is that a lot can change in one hour so we might want smaller snaps more often to get a better look at what is actually happening in the Database when doing performance and tuning. So here is what I am going to do.

In our environment we use the Oracle Scheduler to run this job and as is default it runs as the perfstat user. I connected to the database as perfstat and then I can query the user_jobs table to get the information that I need.

I did the following:

SQL> select job, next_date, interval from user_jobs;

JOB NEXT_DATE

———- ———

INTERVAL

——————————————————————————–

281 21-OCT-08

trunc(SYSDATE+1/24,’HH’)


As you can see the field that I want to change is the INTERVAL. To do this I will need to call the dbms_job.interval() function and figure out the math for the correct interval. I found this Oracle Tips site to be helpful with the math conversion. While I was working on it I was having a bit of difficulty getting it to work. I tried breaking the job in order to change the interval value but you do not have to do this. Below is the PL/SQL that I ran to change the interval.

SQL> begin

2 dbms_job.interval(281, ‘SYSDATE + 3/288′);

3 commit;

4 end;

5 /

PL/SQL procedure successfully completed.

As you can see the dbms_job.interval() function was called upon and I passed it some data. First is the job name. My job is named 281. Then I passed it the Interval that I want the job to run at. So I said take the current system date and then add 3/288. This essentially means run every 15 minutes from this time forward. Below you can see that I am now getting the results that I was looking for.

13942 5 21 Oct 2008 10:00:02 fire440-8

13943 5 21 Oct 2008 11:00:04 fire440-8

13944 5 21 Oct 2008 12:42:09 fire440-8

13945 5 21 Oct 2008 13:00:04 fire440-8

13946 5 21 Oct 2008 13:15:06 fire440-8

13947 5 21 Oct 2008 13:30:08 fire440-8

You will notice that the top snap was at 10:00 and then 11:00 then I broke the job so it did not run until I un-broke the job. From 13:00 you can see that it is indeed running every 15 minutes. If you have any questions or comments please let me know. Again you can check out my site at sysadminsmith.com if you would like some help with this or any other issue and check out some of my other blog postings. Thanks and I hope you learned something from this. I know I did.


October 20, 2008  11:50 PM

MS SQL Server Monitoring



Posted by: Colin Smith
Database, DBA, Microsoft Windows, Powershell, SQL Server

One of the first tasks that was assigned to me after moving to the DBA team was to find and implement a monitoring solution for MS SQL Server instances. We wanted to do this in house, if possible, in order to save money. In order to do this I decided to try and use Windows Powershell. Before I could start coding I had to determine exactly what I wanted to be able to accomplish with this monitoring. I took some time and worked with the other members in my team and came up with the following.

  1. Dynamically determine what day and time it is and who is on call for that time. Send notifications of problems to the correct people based on these variables.
  2. Determine what errors are important to us and what notifications are not important to us.
  3. Continue sending out notifications every ten minutes until someone responds to the error.
  4. Have a way to respond to issues.

That is the very basics. At first we just wanted to be able to get errors and respond essentially. I figured that I would need at least two scripts to accommodate this. I will need one script that will monitor the ERRORLOG file and one that will allow us to respond to the error and let the script know that we have responded. This all sounds simple enough but I soon found out that it is not so easy.

My team has a listing of every SQL Server instance in the company, but since I am the new guy I wanted to verify that I was not missing any. I found a program called SQL Recon that scans the network and reports back on all SQL Server instances on the network. I ran this utility, sifted through the results and found that most but not all of the instances were documented. I documented the missing servers and then started to figure out how to monitor them all.

I know that I want to monitor the ERRORLOG file that SQL Server writes to for any errors that occur. Using Powershell it is easy enough to read in a file and scan each line for keywords or phrases. IE..

$sqlerrors = Get-Content “C:\ERRORLOG”

Echo “############ Contents of the Errorlog File ##################”

$sqlerrors

Echo “############ Contents of the Errorlog File ##################`n”

foreach ($sqlerror in $sqlerrors)

{

        if ($sqlerror -like “*error*”)

        {

            echo “`nI found the word ERROR in this line:”

            $sqlerror

        }

}

This code will get you the following Output.


PS C:\Documents and Settings\smithc> . ’C:\DOCUME~1\smithc\LOCALS~1\Temp\Untitled4.ps1′
############   Contents of the Errorlog File   ##################
Sample log file
Error on this line
nothing on this line
Sev on this line
Error on this line that is due to a logon
############   Contents of the Errorlog File   ##################

I found the word ERROR in this line:
Error on this line

I found the word ERROR in this line:
Error on this line that is due to a logon
PS C:\Documents and Settings\smithc>

So as you can see reading the log and extracting just the lines that I want is fairly straightforward, the first big problem comes up with finding a way to only find errors that I have not already dealt with. I need to find a way to tell the script where to start caring about the errors in the file. I figured that it would be easy to put a Marker in the ERRORLOG file and the script could use that marker as a way to know what errors are old and what errors are new. No I ask a question to you. How do you edit the ERRORLOG file since the SQL Server Agent always has it locked? That is the first big hurdle and we will deal with this next time.

Thanks for checking out my first Blog entry here and I hope that you are enjoying it and I hope that this will be useful to someone out there. This script could be modified to monitor any application that has an out file. If you have any questions about this please leave some comments or head over to my website at sysadminsmith.com. I will do anything I can to help you out and I also do not mind learning from you. 

Check out part 2 of this series at MS SQL Server Monitoring Part 2


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: