VMWare networking and Windows firewall

Couple of days ago a colleague and I were trying to set up 3 virtual windows 2008 servers in a domain (on VMWare's ESX as it happens). One domain controller (DC), one web and a database. Simple enough you'd think.

So we had a virtual switch set up routing to the DC with DHCP running and the other two servers set to dynamically pick-up IP addresses. However try as we might we couldn't get them to 'see' the DC using ping etc, yet everything looked OK on the switch and the TCP/IP settings on the servers. So we scratched our heads for a while - and I left for a meeting saying 'its as though we have a loose bit of virtual CAT5...'.

When I returned my colleague had cracked it. As you may have guessed from the title of this post it was Windows firewall all along. Turning it off on all 3 servers brought the network into life. Bit of a forehead-slapping 'doh' moment...

SQL Agent job schedules

Last week I found myself reviewing a SQL Server (2000 vintage no less!). As part of the review I was asked to document the Agent jobs - when they ran, how long for etc, etc. Unfortunately there were well over 100 of them. Clearly a query was called for - and to save me from having to repeat the exercise I'll reproduce it here:

This is the 2000 version:

select j.name,
case freq_type when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly Relative'
else 'On start' end as TypeOfFrequency,
case freq_type when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly Relative'
else 'On start' end,
case when freq_type=4 then
case when freq_interval=1 then 'Every day'
else 'Every '+cast(freq_interval as varchar(2))+' days' end
when freq_type=8 then 'Every '+
left(
case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end,
len(case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end)-1)
when freq_type=16 then 'On day '+cast(freq_interval as varchar(2))+' of the month'
else 'The '+
case freq_relative_interval when 1 then 'first '
when 2 then 'second '
when 4 then 'third '
when 8 then 'forth '
else 'last ' end
+'day of the month'
end,
case freq_subday_type when 1
then 'At ' + reverse(left(reverse(next_run_time), 2)+':'
+ substring(reverse(next_run_time), 3, 2)+':'
+ substring(reverse(next_run_time), 5, 2))

when 2 then 'Every '+cast(freq_subday_interval as varchar(1000))+' seconds'
when 4 then 'Every '+cast(freq_subday_interval as varchar(1000))+' mins'
else 'Every ' +cast(freq_subday_interval as varchar(1000))+' hours'
end as 'Daily execution (hhmmss)',
avg(run_duration) AvgDuration,
min(run_duration) MinDuration,
max(run_duration) MaxDuration,
count(*)
from sysjobs j
inner join sysjobhistory h on j.job_id=h.job_id
inner join sysjobschedules s on j.job_id=s.job_id
group by
j.name,
case freq_type when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly Relative'
else 'On start' end,
case when freq_type=4 then
case when freq_interval=1 then 'Every day' else 'Every '+cast(freq_interval as varchar(2))+' days' end
when freq_type=8 then 'Every '+
left(case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end,
len(case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end)-1)
when freq_type=16 then 'On day '+cast(freq_interval as varchar(2))+' of the month'
else 'The '+
case freq_relative_interval when 1 then 'first '
when 2 then 'second '
when 4 then 'third '
when 8 then 'forth '
else 'last ' end
+'day of the month'
end,
case freq_subday_type when 1 then 'At ' + reverse(left(reverse(next_run_time), 2)+':'
+ substring(reverse(next_run_time), 3, 2)+':'
+ substring(reverse(next_run_time), 5, 2))
when 2 then 'Every '+cast(freq_subday_interval as varchar(1000))+' seconds'
when 4 then 'Every '+cast(freq_subday_interval as varchar(1000))+' mins'
else 'Every ' +cast(freq_subday_interval as varchar(1000))+' hours'
end

order by j.name

Nasty huh! Not sure why bitmask values are being used where they aren't combined (ie freq_relative_interval) but anyway... Note also the smart-alec use of the bitwise OR operator to get the days of the week.

This is the 2005/8 version. Note that since you can now have multiple schedules per job, you might get multiple rows per job - hence the use of 'ScheduleName':

select j.name,
s.name as ScheduleName,
case freq_type when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly Relative'
else 'On start' end as TypeOfFrequency,
case when freq_type=4 then
case when freq_interval=1 then 'Every day' else 'Every '+cast(freq_interval as varchar(2))+' days' end
when freq_type=8 then 'Every '+
left(
case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end,
len(case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end)-1)
when freq_type=16 then 'On day '+cast(freq_interval as varchar(2))+' of the month'
else 'The '+
case freq_relative_interval when 1 then 'first '
when 2 then 'second '
when 4 then 'third '
when 8 then 'forth '
else 'last ' end
+'day of the month'
end as Interval,
case freq_subday_type when 1 then 'At ' + reverse(left(stuff('000000', 6-len(cast(active_start_time as varchar(6))), len(cast(active_start_time as varchar(6))), cast(active_start_time as varchar(6))), 2))+':'+
substring(reverse(stuff('000000', 6-len(cast(active_start_time as varchar(6))), len(cast(active_start_time as varchar(6))), cast(active_start_time as varchar(6)))), 3, 2)+':'+
substring(reverse(stuff('000000', 6-len(cast(active_start_time as varchar(6))), len(cast(active_start_time as varchar(6))), cast(active_start_time as varchar(6)))), 1, 2)
when 2 then 'Every '+cast(freq_subday_interval as varchar(1000))+' seconds'
when 4 then 'Every '+cast(freq_subday_interval as varchar(1000))+' mins'
else 'Every ' +cast(freq_subday_interval as varchar(1000))+' hours'
end as 'Daily execution (hhmmss)',
avg(run_duration) AvgDuration,
min(run_duration) MinDuration,
max(run_duration) MaxDuration

from sysjobs j
left outer join sysjobhistory h on j.job_id=h.job_id
left outer join sysjobschedules js on j.job_id=js.job_id
left outer join dbo.sysschedules s on js.schedule_id=s.schedule_id
group by
j.name,
s.name,
case freq_type when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly Relative'
else 'On start' end,
case when freq_type=4 then
case when freq_interval=1 then 'Every day' else 'Every '+cast(freq_interval as varchar(2))+' days' end
when freq_type=8 then 'Every '+
left(case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end,
len(case when 1|freq_interval=freq_interval then 'Sunday, ' else '' end
+case when 2|freq_interval=freq_interval then 'Monday, ' else '' end
+case when 4|freq_interval=freq_interval then 'Tuesday, ' else '' end
+case when 8|freq_interval=freq_interval then 'Wednesday, ' else '' end
+case when 16|freq_interval=freq_interval then 'Thursday, ' else '' end
+case when 32|freq_interval=freq_interval then 'Friday, ' else '' end
+case when 64|freq_interval=freq_interval then 'Saturday,' else '' end)-1)
when freq_type=16 then 'On day '+cast(freq_interval as varchar(2))+' of the month'
else 'The '+
case freq_relative_interval when 1 then 'first '
when 2 then 'second '
when 4 then 'third '
when 8 then 'forth '
else 'last ' end
+'day of the month'
end,

case freq_subday_type when 1 then 'At ' + reverse(left(stuff('000000', 6-len(cast(active_start_time as varchar(6))), len(cast(active_start_time as varchar(6))), cast(active_start_time as varchar(6))), 2))+':'+
substring(reverse(stuff('000000', 6-len(cast(active_start_time as varchar(6))), len(cast(active_start_time as varchar(6))), cast(active_start_time as varchar(6)))), 3, 2)+':'+
substring(reverse(stuff('000000', 6-len(cast(active_start_time as varchar(6))), len(cast(active_start_time as varchar(6))), cast(active_start_time as varchar(6)))), 1, 2)
when 2 then 'Every '+cast(freq_subday_interval as varchar(1000))+' seconds'
when 4 then 'Every '+cast(freq_subday_interval as varchar(1000))+' mins'
else 'Every ' +cast(freq_subday_interval as varchar(1000))+' hours'
end

order by j.name

Kilimanjaro now 2008 R2 and Madison CTP out in July

Kilimanjaro, previously the code name of the next release of SQL Server is now SQL Server 2008 R2.

The list of features is pretty long for an 'R2' - the link above provides the full details, but as a hopelessly biased BI chap, the most interesting one for me is the technology previously known as Gemini. This is MS's answer to the column-orientated 'in-memory' analysis engines of vendors such as QlikTech. The ability to be able to provide users with an ability to analyse millions of rows of data through Excel at 'lightening fast speeds' without needing to create cubes, write MDX etc is an interesting one, to say the least. No confirmed date for this but Mary-Jo Foley reckons it will be July.

For me though the most exciting announcement is that a Madison CTP is also planned for July. The lack of a compelling scalability story has always been a bit of a chink in SQL Server's armour, particularly when compared to vendors such as Teradata and Netezza, and I will be all over this when it comes out.

What both of these announcements show is that SQL Server is forging ahead, addressing weaker product areas and - providing the CTP's ship on time - on schedule.

Microsoft Connectors for Oracle and Teradata by Attunity

Thanks to Euan Garden for mentioning that MS have licensed Attunity's Oracle and Teradata 'Connect adapters' (ie database providers) for use with SSIS 2008 (Attunity press release here).

I've known about Attunity's providers for a while, but the licensing deal is great news, because paying for database providers is a difficult case to make. Anyone using Oracle for a source or destination in a SSIS package should investigate using these since, as I have posted before, there are a number of bugs with Oracle providers. I've yet to try the Attunity providers myself so I'll post again when I've tried them out. However, its hard to see they could be any worse than Oracle's.

Anyway you can download them here.

Deploying Reporting Services programmatically - shared data sources

Hello again! After a longish hiatus I'm back posting fairly useful stuff - so I don't lose/forget/have to do it again in another job.

The first useful snippet covers deploying Reporting Services reports using the rs utility on the command line. This will be necessary in situations where you can't deploy directly from BIDS - which to my mind isn't good practice when you are outside of the development environment. Although there are various examples of doing this there aren't many using VB.NET (which you have to use with rs) and none covering my requirements.

As you may know when using the rs utility to deploy (technically create) reports on a Report Server it does not maintain references to shared data sources even when they exist in the same relative position given in the Reporting Services project. This means you have the tricky job of adding those references programatically after using CreateReport to create the reports.

Tricky? Well it is if your reports have multiple different data sources. If you try adding a data source to a report that doesn't know about that data source (ie hasn't got a reference to it in the .rdl) you will get an error. Therefore you have to work out what data sources that report has a reference to and relate those references to the shared data sources with the same name elsewhere in Reporting Services. A complicating factor is that you can't (or at least I couldn't) simply map the data sources obtained from the report to the shared data source location. Which is way I ended up looping around the datasources retrieved from the report definition in the code below. Anyway it works and that's good enough for me...

In order to use the example create an empty .rss file in Notepad and copy and paste the following code and save it as UpdateDataSources.rss.

Public Sub Main()

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim item as CatalogItem
Dim items as CatalogItem()

Try
items=rs.ListChildren(ReportFolder, False)

For Each item in items

If item.Type = 2 'ie Report

Dim dataSources() as DataSource = rs.GetItemDataSources(item.Path)

For Each ds as DataSource in dataSources

Dim sharedDs(0) as DataSource
sharedDs(0)=GetDataSource(SharedDataSourceFolder, ds.Name)

rs.SetItemDataSources(item.Path, sharedDs)
Console.WriteLine("Set " & ds.Name & " datasource for " & item.Path & " report")

Next

end if


Next

Console.WriteLine("Shared data source reference set for {0} reports.", ReportFolder)

Catch e As SoapException
Console.WriteLine(e.Detail.InnerXml.ToString())
End Try

End Sub

Private Function GetDataSource(sharedDataSourcePath as string, dataSourceName as String) as DataSource


Dim reference As New DataSourceReference()
Dim ds As New DataSource

reference.Reference = sharedDataSourcePath & "/" & dataSourceName
ds.Item = CType(reference, DataSourceDefinitionOrReference)

ds.Name = dataSourceName

GetDataSource=ds


End Function

Use it with rs on the command-line as follows:

rs -i UpdateDataSources.rss -v ReportFolder=[Folder Location containing the reports to be updated] -v SharedDataSourceFolder=[Folder location containing the shared data sources].

SQL Bits III video session on Data Vizualisation

Interesting video here ('web cast' maybe? What's the difference?) about visualising data - focusing on tables, graphs and dashboards. If you don't know what a 'bullet graph', or a 'sparkline' is then this is for you. Most of it is non-product specific as well.

May sound a bit dry but this is one area of BI you can't ignore and can result in all of the behind-the-scenes effort going to waste.

Child vs Parent Package Configurations contd..

Not so much a Part 2 (I will get round to it honest..) but a real-world example of the advantage of parent configurations. My current project involves migrating data for a major Norwegian bank, and needless to say we are using a parent package to set the configuration of the child packages actually doing the migration.

As often happens in IT projects we weren't in posession of all of the requirements before we started development. One of the requested changes was significant. The client wanted to use multiple target environments for each release. In other words, our migration server would need to migrate data into n target servers. However, we had baked the target connection string into a database table, which was read by the parent package, so we would have to update the database table every time we wanted to migrate to a different target. I felt this would be a cumbersome and risky operation at runtime. Not only could the connection string be incorrectly updated but we ran the risk of updating the wrong configuration or even wiping out all the configurations. On top of that, there was the possibility that the client might be making the changes themselves. Therefore I thought a change to our package config was required.

The option that seemed most sensible in the circumstances was to allow the target to be set at runtime on the command-line. We were using dtexec to run the migration anyway and using the /SET parameter you can set package properties including package variables (you should always set properties through variables as this insulates yourself against changes to packages).

What has all this got to do with child vs parent package configurations? Well, to make this change for us was simple - we only had to change the parent package and delete the configuration from the database table. If we had been configuring child packages directly we would have had to change every child package using that configuration. As we were doing lookups against the target server this would have been most of the child packages. As the project stands that would have been 3 packages - but had we decided to this in a months time it would probably be 6-8. With that many to do there would have been quite an impact on the project.

Therefore be aware that the most unlikely changes can be requested sometimes, and therefore its almost always best to try and anticipate these changes in your configuration design. Particularly when the costs of such a design are (in my view at least) relatively trivial.