Tuesday, 23 April 2013

PowerShell Script to find SQL Agent Job details on a SQL server instance

The below script helps to find SQL Agent Job details on a SQL server instance
 
1:  # Script to find SQL Agent Job details on a SQL server instances  
2:  # Created by - Vinoth N Manoharan  
3:  # Version 1.1  
4:  # Date - 15/09/2011  
5:  # Script Help :-  
6:  #---------------  
7:  # Parameter 1 :- "-s" to run powershell for Single Instance of SQL  
8:  # Parameter 2 :- SQL Server name  
9:  # Example1:- FindSQLJob.ps1 -s <SQLservername\instancename>  
10:  Clear-Host  
11:  $List =@()  
12:  $srv = @()  
13:  $srvAgt = @()  
14:  $srvjob = @()  
15:  $schedule = @()  
16:  $Jobfrequency = $null  
17:  $jobout =@()  
18:  $joboutstr = @()  
19:  $srvjobname =$null  
20:  $srvjobstate = $null  
21:  if($args.Length -ne 2)  
22:  {  
23:  Write-Host "Incorrect Paramenter Count use either -s to specify the servername/Serverlist"  
24:  }  
25:  elseif(($args[0] -eq "-s") -or ($args[0] -eq "-S"))  
26:  {  
27:  $computer = $args[1]  
28:  if($computer -ne $null)  
29:  {  
30:  $srt = "Server Name :- " + $computer  
31:  $srt  
32:  Echo "*************************"  
33:  Echo "`n"  
34:  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null  
35:  $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"  
36:  $srvAgt = $srv.JobServer  
37:  foreach($srvjob in $srvAgt.Jobs)  
38:  {  
39:  $srvjobname = 'JobName : '+ $srvjob.Name  
40:  $srvjobstate = 'JobEnabled : ' + $srvjob.IsEnabled  
41:  #Echo "`n"  
42:  Echo $srvjobname  
43:  Echo $srvjobstate  
44:  $jobcnt = 0  
45:  $jobout = @()  
46:  $joboutstr = @()  
47:        ForEach($schedule in $srvjob.JobSchedules)  
48:        {  
49:        $FrequencyTypes = $schedule.FrequencyTypes  
50:        $FrequencyInterval = $schedule.FrequencyInterval  
51:        $FrequencyRecurrenceFactor = $schedule.FrequencyRecurrenceFactor  
52:        $FrequencyRelativeIntervals = $schedule.FrequencyRelativeIntervals  
53:        $FrequencySubDayInterval = $schedule.FrequencySubDayInterval  
54:        $FrequencySubDayTypes = $schedule.FrequencySubDayTypes  
55:        $jobcnt = $jobcnt + 1  
56:        $jobcntstr = $null  
57:        $x = @()  
58:        $jobout = @()  
59:        $y = $null  
60:        $cnt = $null  
61:        $cnt1 = $null  
62:        $z = $null  
63:        $str1 = $null  
64:        $str2 = $null  
65:        $str3 = $null  
66:        $str4 = $null  
67:        $str5 = $null  
68:        $str6 = $null  
69:        $x11 = @(  
70:                @(1,"Sunday"),  
71:                @(2,"Monday"),  
72:                @(4,"Tuesday"),  
73:                @(8,"Wednesday"),  
74:                @(16,"Thursday"),  
75:                @(32,"Friday"),  
76:                @(64,"Saturday")  
77:              )     
78:            if($FrequencyTypes -eq "Daily")  
79:            {  
80:             $Jobfrequency = "Runs every " +$FrequencyInterval+ " Day/Days"     
81:            }  
82:            elseif($FrequencyTypes -eq "Weekly")  
83:            {  
84:             Switch ($FrequencyInterval)  
85:              {  
86:               1{$Weeklyschday = "Sunday"}  
87:               2{$Weeklyschday = "Monday"}  
88:               4{$Weeklyschday = "Tuesday"}  
89:               8{$Weeklyschday = "Wednesday"}  
90:               16{$Weeklyschday = "Thursday"}  
91:               32{$Weeklyschday = "Friday"}  
92:               64{$Weeklyschday = "Saturday"}  
93:               62{$Weeklyschday = "WeekDays"}  
94:               65{$Weeklyschday = "WeekEnds"}  
95:               127{$Weeklyschday = "EveryDay"}  
96:               Default {  
97:                       $x = @(1,2,4,8,16,32,64)  
98:                          $itr = 0  
99:                          $cnt = 0  
100:                          $cnt1 = 0  
101:                          $cnt2 = 0  
102:                          $cnt3 = 0  
103:                          $cnt4 = 0  
104:                          $flag = 0  
105:                          $day1 = 0  
106:                          $day2 = 0  
107:                          $day3 = 0  
108:                          $day4 = 0  
109:                          $day5 = 0  
110:                          $day6 = 0  
111:                          $total = 0  
112:                          $total1 = 0  
113:                          $total2 = 0  
114:                          $total3 = 0  
115:                          $total4 = 0  
116:                          for($itr=0;$itr -le 5;$itr++)  
117:                          {  
118:                             $itr1 = $itr  
119:                             $cnt = $itr + 1  
120:                             $cnt1 = $itr + 2  
121:                             $cnt2 = $itr + 3  
122:                             while($cnt -le 6)  
123:                             {  
124:                                $total = $x[$itr]+$x[$cnt]  
125:                                if($FrequencyInterval -eq $total)  
126:                                {  
127:                                   $flag = 2  
128:                                   $day1 = $itr  
129:                                   $day2 = $cnt  
130:                                }  
131:                                #write-host $x[$itr] '+' $x[$cnt] '=' $total  
132:                                $temp = $cnt1  
133:                                while($cnt1 -le 6)  
134:                                {  
135:                                   $total1 = $x[$itr]+$x[$cnt]+$x[$cnt1]  
136:                                   if($FrequencyInterval -eq $total1)  
137:                                   {  
138:                                         $flag = 3  
139:                                         $day1 = $itr  
140:                                         $day2 = $cnt  
141:                                         $day3 = $cnt1  
142:                                   }  
143:                                   #Write-host $x[$itr] '+' $x[$cnt] '+' $x[$cnt1] '=' $total1  
144:                                   #Write-Host $itr '--' $cnt '--' $cnt1 '--' $cnt2  
145:                                   $cnt2 = $cnt1 + 1  
146:                                   While($cnt2 -le 6)  
147:                                   {  
148:                                      $total2 = $x[$itr]+$x[$cnt]+$x[$cnt1]+$x[$cnt2]  
149:                                      if($FrequencyInterval -eq $total2)  
150:                                      {  
151:                                         $flag = 4  
152:                                         $day1 = $itr  
153:                                         $day2 = $cnt  
154:                                         $day3 = $cnt1  
155:                                         $day4 = $cnt2  
156:                                      }  
157:                                      #Write-host $x[$itr] '+' $x[$cnt] '+' $x[$cnt1] '+' $X[$cnt2] '=' $total2  
158:                                      #Write-Host $itr '--' $cnt '--' $cnt1 '--' $cnt2  
159:                                      $cnt3 = $cnt2 + 1  
160:                                      While($cnt3 -le 6)  
161:                                      {  
162:                                         $total3 = $x[$itr]+$x[$cnt]+$x[$cnt1]+$x[$cnt2]+$x[$cnt3]  
163:                                         if($FrequencyInterval -eq $total3)  
164:                                         {  
165:                                            $flag = 5  
166:                                            $day1 = $itr  
167:                                            $day2 = $cnt  
168:                                            $day3 = $cnt1  
169:                                            $day4 = $cnt2  
170:                                            $day5 = $cnt3  
171:                                         }  
172:                                         #Write-host $x[$itr] '+' $x[$cnt] '+' $x[$cnt1] '+' $X[$cnt2] '+' $x[$cnt3] '=' $total3  
173:                                         $cnt4 = $cnt3 + 1  
174:                                         While($cnt4 -le 6)  
175:                                         {  
176:                                            $total4 = $x[$itr]+$x[$cnt]+$x[$cnt1]+$x[$cnt2]+$x[$cnt3]+$x[$cnt4]  
177:                                            if($FrequencyInterval -eq $total3)  
178:                                            {  
179:                                               $flag = 6  
180:                                               $day1 = $itr  
181:                                               $day2 = $cnt  
182:                                               $day3 = $cnt1  
183:                                               $day4 = $cnt2  
184:                                               $day5 = $cnt3  
185:                                               $day6 = $cnt4  
186:                                            }  
187:                                            #Write-host $x[$itr] '+' $x[$cnt] '+' $x[$cnt1] '+' $X[$cnt2] '+' $x[$cnt3] '+' $x[$cnt4] '=' $total4  
188:                                            $cnt4 = $cnt4 + 1  
189:                                         }  
190:                                         $cnt3 = $cnt3 + 1  
191:                                      }  
192:                                      $cnt2 = $cnt2 + 1  
193:                                   }  
194:                                   $cnt1 = $cnt1 + 1  
195:                                }  
196:                                $cnt1 = $temp  
197:                                $cnt = $cnt + 1  
198:                                $cnt1 = $cnt1 + 1  
199:                                #Write-Host $cnt '--' $cnt1 '--' $cnt2  
200:                             }  
201:                          }  
202:                          Switch($flag)  
203:                          {  
204:                          0{$Weeklyschday = "No Corresponding Value found in the Schedule"}  
205:                          2{  
206:                            $str1 = ($x11[$day1][1])  
207:                            $str2 = ($x11[$day2][1])  
208:                            $Weeklyschday = $str1 + '--' + $str2  
209:                           }  
210:                          3{  
211:                            $str1 = ($x11[$day1][1])  
212:                            $str2 = ($x11[$day2][1])  
213:                            $str3 = ($x11[$day3][1])  
214:                            $Weeklyschday = $str1 + '--' + $str2 + '--' + $str3  
215:                           }  
216:                          4{  
217:                            $str1 = ($x11[$day1][1])  
218:                            $str2 = ($x11[$day2][1])  
219:                            $str3 = ($x11[$day3][1])  
220:                            $str4 = ($x11[$day4][1])  
221:                            $Weeklyschday = $str1 + '--' + $str2 + '--' + $str3 + '--' + $str4  
222:                           }  
223:                          5{  
224:                            $str1 = ($x11[$day1][1])  
225:                            $str2 = ($x11[$day2][1])  
226:                            $str3 = ($x11[$day3][1])  
227:                            $str4 = ($x11[$day4][1])  
228:                            $str5 = ($x11[$day5][1])  
229:                            $Weeklyschday = $str1 + '--' + $str2 + '--' + $str3 + '--' + $str4 + '--' + $str5  
230:                           }  
231:                          6{  
232:                            $str1 = ($x11[$day1][1])  
233:                            $str2 = ($x11[$day2][1])  
234:                            $str3 = ($x11[$day3][1])  
235:                            $str4 = ($x11[$day4][1])  
236:                            $str5 = ($x11[$day5][1])  
237:                            $str6 = ($x11[$day5][6])  
238:                            $Weeklyschday = $str1 + '--' + $str2 + '--' + $str3 + '--' + $str4 + '--' + $str5 + '--' + $str6  
239:                           }  
240:                          }  
241:                      }             
242:              }  
243:              $Jobfrequency = "Runs on "+$Weeklyschday  
244:            }elseif($FrequencyTypes -eq "OneTime")  
245:            {  
246:             $Jobfrequency = "Runs only One Time"          
247:            }elseif($FrequencyTypes -eq "Monthly")  
248:            {  
249:             $Jobfrequency = "Runs every " + $FrequencyInterval +" day of the month"  
250:            }elseif($FrequencyTypes -eq "MonthlyRelative")  
251:            {  
252:              Switch ($FrequencyInterval)  
253:              {  
254:               1{$Weeklymnday = "Sunday"}  
255:               2{$Weeklymnday = "Monday"}  
256:               3{$Weeklymnday = "Tuesday"}  
257:               4{$Weeklymnday = "Wednesday"}  
258:               5{$Weeklymnday = "Thursday"}  
259:               6{$Weeklymnday = "Friday"}  
260:               7{$Weeklymnday = "Saturday"}  
261:               8{$Weeklymnday = "EveryDay"}  
262:               9{$Weeklymnday = "WeekDays"}  
263:               10{$Weeklymnday = "WeekEnds"}  
264:              }  
265:              $Jobfrequency = "Runs every "+$FrequencyRelativeIntervals+" "+$Weeklymnday+ " of the month"  
266:            }else{$Jobfrequency = "No Valid schedule specified"}  
267:           $jobout = $schedule  
268:           #Echo "`n"  
269:           #$jobcntstr = "Schedule:"+$jobcnt+":-"  
270:           #$jobcntstr  
271:           $jobout = $jobout | Add-Member JobSchedule -MemberType NoteProperty -Value $Jobfrequency -PassThru|Add-Member -MemberType AliasProperty -name ScheduleName -value Name -SecondValue System.String -PassThru|  
272:           Add-Member -Name Schedule -MemberType NoteProperty -Value $jobcnt -PassThru  
273:           #|Format-List -Property ScheduleName,IsEnabled,JobSchedule,ActiveStartTimeOfDay  
274:           $joboutstr += $jobout  
275:        }  
276:  $joboutstr | Format-Table -Property Schedule,ScheduleName,IsEnabled,FrequencyTypes,JobSchedule,ActiveStartTimeOfDay,ActiveStartDate,ActiveEndDate -AutoSize  
277:  }  
278:  }  
279:  }  

Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Tuesday, 16 April 2013

SQL Server Cluster Installation AD Permission error SQL Server 2012 and SQL Server 2008R2


Recently when I was Installing SQL Server 2012 and SQL Server 2008 R2 Cluster, the installation completed successfully but the SQL Resources failed with the below error,

Cluster network name resource 'SQL Network Name (<SQLNetworkName>)' failed to create its associated computer object in domain 'DomainName' during: Resource online.

The text for the associated error code is: Access is denied.


Please work with your domain administrator to ensure that:
- The cluster identity '<Clustername>$' has Create Computer Objects permissions. By default all computer objects are created in the same container as the cluster identity ‘<Clustername>$'.
- The quota for computer objects has not been reached.
- If there is an existing computer object, verify the Cluster Identity ‘<Clustername>$' has 'Full Control' permission to that computer object using the Active Directory Users and Computers tool.

This error is due to the permission issue within the OU of the Cluster identity in the AD Server.The install creates computer objects in a staging area of AD and tries to move it to the Cluster OU when the resources are brought online. 

To resolve this either you might have to ask the Windows Admin to delegate access within Cluster OU or ask them to move the Objects from Staging Area to Cluster OU manually. Remember the SQL Server install was successful so once the permission is grated all you need to do is bring the SQL Services online.

The below MSDN article has an detailed explanation of the architecture based on the above problem(http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx#BKMK_steps_installer)