I picked up a number a couple of questions from Thursday night's TechNet Road show in London including a couple on dependencies in SQL Server 2008. So here's the answers...
Firstly there are two sorts of dependencies:
Dependencies are updated when a referring object is created and rely on names so if you want to rely on this then you need to use proper four part names i.e. MyServer.MyDatabase.MyTable.MyColumn.
Note that you can get dependencies across linked servers but this will only work with the four part naming convention and not EXEC ('…') AT linked_srv1
The next question I got asked was how synonyms work with dependencies. so here's an example:
-- Create a synonym for the Product table in AdventureWorks. USE AdventureWorksDW CREATE SYNONYM dbo.MyProduct FOR AdventureWorks.Production.Product; GO
-- check the dependency for MyView SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name ,referenced_server_name AS server_name ,referenced_database_name AS database_name ,referenced_schema_name AS schema_name , referenced_entity_name FROM sys.sql_expression_dependencies WHERE referencing_id = OBJECT_ID(N'dbo.MyProc1');
All this shows is the dependency on the synonym
If a view is created against the table directly i.e.
CREATE VIEW dbo.MyView AS SELECT Name,ProductNumber FROM AdventureWorks.Production.Product;
..then the dependency reports correctly.
I have to say I don't think that's a great story and you get the same information back from the management console if you right click on an object and select view dependencies.
Like Salvador Dali I have hung up my watch. I am in Barcelona to enjoy a bit of culture now TechEd is over.
However I did notice that the next beta of SQL Server 2008 (CTP5) which has about 75% of the functionality of the final release and is now available on the connect site to download.
Some of the new and interesting stuff in there includes:
It seems every week we are bombarded with tons of surveys, that scare us into eating more of this and generally less of everything. Surveys on database health and security are much rarer, but I did notice that David Lichfield is about to publish that latest edition of The Database Exposure Survey 2007.
I would expect that there are a few databases out there that are vulnerable but I was surprised how high this figure is generally and also that it applies more to Oracle than to SQL Server. The basic problem is that the versions in use have known vulnerabilities where the latest versions are better able to deal with threats. To counter this Oracle and Microsoft release patches and best practice advice, but customers are simply not applying the patches or following the advice.
I am not an expert on Oracle but I am sure they are just as keen as we are to help close the gaps and you should contact your reseller and crawl the extensive help on their website. Where I can help is to suggest a few pointers for SQL Server 2000:
Of course if you do want a rapid career change then please ignore this.
Blogging is pretty new for me and it's difficult to know how interesting or valuable my stuff is. So I am pleased that my thoughts on providing a way to get the standard SQL server reports to run across multiple servers have generated some interest.
I would agree that what I wrote was a lash up that is only really appropriate for a few servers and is quite labour intensive. For the big enterprise there is System Center which can do this kind of thing, but not everyone is going to make that investment.
One of my new friends, Buck Woody in the SQL server product team has quite a few posts on this subject on his blog and he also sent me a link where the report definitions (RDL) for the standard reports can be found so you won't need to use profiler to trap the SQL used.
That's why I blog, to learn and pass it on.
Along time ago the BBC had just the one TV channel it's shot up to four now we are digital. Microsoft are in on the act. Originally there was only Channel 9 which is named after the radio channel you can listen to pilots on in commercial aircraft however it's really aimed at developers, developers, developers.
Unlike the BBC Microsoft channels are intended for particular audiences and the line-up goes like this:
If you have content you would like to be on TV then get back to me and if not just tune in and turn on.
I am manning the ask the experts SQL server booth at TechEd this week. One of the many interesting questions I was asked was how the reporting information from the standard reports in SQL server could be integrated into one set of data from multiple servers.
I couldn't think of anything immediately to help him, so I am posting my thoughts here.
The reports are using Reporting Services from a SQL source, but I couldn't find out what this was without running profiler - so that's what I had to do. I setup a new trace to watch for RPC complete events with a column filter on application name set to "Microsoft SQL Server Management Server". I then ran the Activity - All Session report:
and then stopped the trace and looked at the relevant event:
as you can see the SQL looks long but it's mainly columns, there are only four tables involved:
Of course in some of the other reports there are multiple charts and data regions so the underlying query will be much more complex, but this can be easily broken down and will only hit four or five table for each sub-query.
The next step is how to combine and extract this. Personally I would use Integration Services to run this query for each server with the query as the source and put this out to one table. On the way throw you would tag every row with the server it came in a new column. the final step would ne to fire this out to excel and schedule it to run as required.
Another approach would be to do the who;e thing in one stored procedure which would link to each server in turn and would run on demand.
It's the little thing that make you smile. I am at TechEd this week at an excellent session by Bob Beauchemin on the T_SQL enhancements in SQL Server 2008.
He popped up a slide with the += operator which allows you add a number to itself in common with many other languages so:
@MyNumber += 2 would add to the number (this also works for minus, multiplication and divide).
Somebody asks Bob "Does that work with strings as well?"
Let's check that Bob says.
declare @myName varchar(12) = 'Bob' set @myName += ' Foo' Print @myName
declare @myName varchar(12) = 'Bob'
set @myName += ' Foo'
Print @myName
that returns 'Bob Foo' which is what you'd expect but good to know.
So top marks to Bob for his presentation style, and to the product guys for making the new function consistent.
There is a new Microsoft Site for that's just been launched, TechNet . It's like the Channel 9 stuff but targeted specifically for IT Pro's, where Channel 9 is focused on the developer. The idea is to create a place where you can get all the stuff you need about what's happening with the latest Microsoft technologies.
So check out the site and we'll keep you posted over the next few weeks as we start filling it with as much stuff as we can lay our hands on.
Viral has working with and blogging about Search Server 2008 and how this can help to find stuff in the enterprise.
I'm a BI evangelist so what can a search tool like those in Search Server and SharePoint do for me?
It would be great if I could search through cubes and reports to answer questions like "How are my Sales doing this week?" So I was very pleased to discover that Guy Mounier from BA insight has written a white paper on exactly how to do that over the Microsoft BI platform by using the Excel services and the Business Data Catalog features in SharePoint 2007.
If you aren't using the Microsoft BI platform yet then BA Insight have similar solutions for the other major ERP/CRM vendors such as Oracle, SAP, PeopleSoft, Siebel.
I was made in Scotland, my grandmother is from Newcastle and I only live in the south because my wife hates the cold! If I did live up north, I am sure I would be complaining, like many people there, that everything happens in London, such as the Olympics and the best shows including of course our TechNet road shows.
Back in September we did head north for our TechNet road shows and Steve & James got a storming reception, so we thought we'd come back for the next tour like you asked us to. However the take-up looks a bit weak, but only outside London where we are packed out. So what's going on?
Like the last road shows we promise not to bore you with endless PowerPoint decks and Marketing speak so get registering here. Failing that please comment on on what you would like to see at a TechNet event in the North.
The IT industry is awash with acronyms, Microsoft is drowning in them and I think we will soon need to move to FLA (Five Letter Acronyms) to keep up - I thought MSRC was the Microsoft Research Centre but Steve insists its' the Microsoft Response Center, and we're both right!
However we are not alone the regulatory and compliance space is also liberally sprinkled with terms like SOX, GLBA, HIPAA, DPA, EUPD as well as a list of international and European standards as long as your arm.
The other thing I have noticed about compliance is that everyone is an expert especially when it comes to the data protection act! Like the Highway Code everyone seems to know it but have you met anyone who has actually read it recently. So if you are wondering how to get compliant and do battle with all those abbreviations and the parts of your organisation who delight in this stuff then check out the TechNet Regulatory Planning Guide.
When was the last time you picked up a land line phone and there was no dial tone? BT are no doubt proud of this but it's not going to make them any headlines, it's much more entertaining and newsworthy to complain about our 3rd world broadband speeds.
The SQL Server product guys are in the same invidious position; We have had over four years with nothing on a our major vulnerabilities report and that's just not going to be a big story on the Register, Fark or Slashdot, so there's no hyperlinks for me to put in.
But it would be niaive to suggest this will continue forever, and there's is a ton of new security stuff in SQL Server 2008. This will ensure we give all our audience something to look forward to; legitimate users can more easily protect their data and terrorists and criminals can stretch their minds coming up with new ways to get it. However from my limited knowledge of security I think they will increasingly rely on the old methods like posing as cleaners and couriers, or rummaging through the bins.
The most often question I get asked about SQL Server 2008 is when is it out? If you are in marketing then there is a world wide launch event in Feb 2008 for Windows server 2008, Visual Studio 2008 and SQL Server 2008. Well that's a bit like the launch of the Mini Clubman on 10 Nov - just because it's launched doesn't mean you can get one immediately. In fact I will be waiting until the SQL Server 2008 launch in February for my Clubman to arrive,
Microsoft and the rest of the software industry is the same i.e. launch doesn't mean ship. So you won't be able to pitch up to the 2008 launch event and walk away with SQL server 2008; it won't be out until later in the year (that's a Microsoft year which runs July to June).
So we're going to have to wait a little longer, but that allows time to evaluate the new release through the beta program, white papers and other technical content on the various sites such as:
Alternatively why not register for an evening of my SQL server 2008 stuff at the TechNet road shows here
Have you ever tried to explain to your mum what you did at work today? For me I had no problem today - all my new friends had a lunchtime party and we all got to make things with Lego. Our teacher Ginger gave us some instructions and James and I got to make an oil rig which rolls out oil drums if you lift the crane. Some of my other chums made a house, trees, a dam and all sorts of stuff and of course this is where the fun started as there were lots of random piles of Lego around the room which meant there was lots of frantic searching for the right bits.
As I keep saying only at Microsoft! There was a very good reason for our fun and that is that we are sponsoring the UK bit of First Lego League. The competition this year is all around energy and is played out around what we made at lunchtime
So if you have got kids get involved and have some serious fun.
My dad, Gordon, was in BI before me, it had a different name but it was definitely BI. I remember him telling me that in 1964 he had this English Electric mainframe with 2k RAM that took up the whole building and it didn't have a divide function. He was working on statistics for the Ministry of Works and needed to compute standard deviation which not only uses division but also square roots.
In the seventies he upgraded like we all do and was using COBOL on ICT (International Computers and Tabulators) hardware which became ICL which became Fujitsu which merged with Siemens etc. The hardware has long gone but I am sure he will be pleased that his beloved COBOL (COmmon Business-Oriented Language) is still alive in its .net guise.
For all you geeks out here here's hello world in COBOL:
IDENTIFICATION DIVISION.
PROGRAM-ID.
HELLO-WORLD.
PROCEDURE DIVISION.
PARAGRAPH-1.
DISPLAY 'Hello, world.'.
STOP RUN.
It did have critics .. "The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense". Edgar Dijkstra which kind of reminds of Socrates crticising the written word. However COBOL was the dominant BI language for twenty years and shows no sign of dying just yet.
As for my Dad, cancer beat him a couple of years ago. Hopefully he's laughing at this blog and telling me I'm a bl**dy fool!
If you are interested in more on what COBOL.Net does then check this.
Microsoft will be turning off mainstream support for SQL server 2000 for most editions of the product in April 2008:
Products Released
General Availability Date
Mainstream Support Retired
Extended Support Retired
SQL Server 2000 64-bit Edition
30/11/2000
08/04/2008
09/04/2013
SQL Server 2000 Developer Edition
SQL Server 2000 Enterprise Edition
SQL Server 2000 Standard Edition
SQL Server 2000 Windows CE Edition 2.0
16/12/2002
08/01/2008
08/01/2013
SQL Server 2000 Workgroup Edition
01/06/2005
Details can be found here
That isn't very far away considering the work needed to test and migrate applications, and I know that some organisations aren't even aware that they may have some of these database lying around.
However if we forget the imperative that this deadline means to those who only want to have supported products in their infrastructure then there are loads of reasons for moving away from SQL server 2000. For me security would have to figure high on this list as SQL server was designed in an age where always on Internet connections were not prevalent as they are today. I wonder how many installations are still out there with no password for sa waiting for a little bit of SQL injection?
Of course SQL server 2000 can be locked down and applying all the patches and fixes is also essential but SQL server 2005 is secure by design and by deployment i.e. out of the box.
Cost is usually mentioned as the main reason for not upgrading, the majority of which is the work involved rather than any license costs. I would say that this is a lot easier now if only because any problem you might encounter has almost certainly been resolved elsewhere by other IT Professionals and this information is widely available not just on the TechNet SQL Server TechCenter SQL but on the SQL community site and in the numerous publications out there.
As ever I'm happy to dive into this in more detail if it's useful.
Only in Microsoft could you get a job in evangelism without being religious. So I suppose I shouldn't be surprised that there are Microsoft clinics without doctors. Clinics do provide help and I found one called what's new in SQL server 2008. It'll take a couple of hours to get the cure, but there isn't much on telly tonight and it'll stop you having a heart attack at the gymn
For those that don't like hospitals I shall continue to spread the word.
If you are left handed like me you notice how much of our language is right minded or right thinking. The same thing happens all over Europe; it is good to be adroit from the french (droit) for right and sinister is from the Italian (sinestre) for left.
At this point any leftie reels out all the cool people from Leonardo to Einstein and Hendrix who were also left handed (& my boss Eileen).
You also get gadgets for left handed people from scissors to guitars and golf clubs, but these aren't always high quality so Hendrix simply bought a right handed stratocaster and played it left handed.
However this clock has to be one of the strangest ideas and this is hanging up in our office in Prague.I couldn't find a vista gadget like this so maybe some left handed developer could help me out.
Try it yourself - simply smash up your right hand like Steve and see how much fun it is to be left handed!
If you want to get an insight of what's hot on the next version of Reporting Services, you can't really do better than get it straight from one of the product managers. So checkout Jason Carlson the Principal Product Manager for reporting services who is presenting a webcast on 1 November.
Of course if reporting isn't your thing then there are these upcoming webcasts out there as well:
I'm not sure if I'm free on the 12th June, but it's good to see we're planning ahead!
Eileen has kitted us out with the new Tanjay IP phones and I have just made room on my desk at home for it! Annoyingly I had signed up for BT's home hub before joining Microsoft doh!
The good stuff is that we can see who's out there either on the display..
or from my mobile or PC. It's very 1984 but at least we don't spend all day looking for each other.
If you call your flagship business intelligence product Performance Point then it might be a good idea to make sure it performs!
Because the product is essentially a suite of front end applications (which is why it's under the office umbrella) it is only going to be as good as what's behind it. At the back-end there is SQL Server, including analysis services, reporting services as well as the database engine which holds all of the dashboards via Sharepoint. There is plenty of information on how these perform by themselves such as the Project Real stuff, but how does all of that work in practice and what if some of you fancy virtualising some of this?
Well I found this white paper hidden away in TechNet on how Performance Point performs. It part of a whole TechCenter devoted to the Performance Point which covers off how to use the tool from both an end-user and an IT Pro perspective.
I would also recommend the IT showcase on Performance Point, which is where we share our experiences of using it internally.
If, like me, you didn't make the launch and you want to see it in action then come to one of these sessions at our next round of TechNet road shows:
If you want to protect your data then the Bit Locker technology in Vista is a good way to go, and I would also recommend Vista backup as it's so simple to use .
Of course if you're in New Zealand and someone has broken into your office and swiped your laptop with your kiwi :-) data like Paul Croucher then you can also use the beer locker approach. This simply entails offering a reward of 12 bottles of beer a month for life - check the links if you don't believe me.
Bit locker is also built into Windows Server 2008 so that if someone lifts your branch server they haven't got your data.
In order to keep the spiraling costs of the education system to a minimum, parents seem to end up with more and more of the bill, and students also get hammered with massive loans. So how about a cheap copy of MS Office ultimate 2007 for £38.95 without any dodgy dealing on an auction site or at a car boot fair. If you only need it for a year then it's only £12.95.
Interested then check the ultimate steal.
Of course this has nothing to do with Business Intelligence but I suspect that if this offer applied to SQL Server or Performance Point then the take up might be bit lower!
As many of you know Whitehaven has gone digital today amongst quite a lot of confusion. I must admit I am struggling to to see the point of staying analog and quite what the fuss is about given that a freeview box costs as little as £20.
The story around Performance Point and ProClarity still seems to be causing similar confusion.
As I have mentioned before Microsoft Performance Point 2007 is a combination of Three Things
ProClarity is the analysis part of Performance Point. The confusion is around migration and upgrades. Essentially if you have the existing ProClarity Analytics Server and maintenance or software assurance you get an equivalent license of Performance Point.
If you aren't ready for Performance Point you can still continue to buy more ProClarity licenses. There is no immediate pressure to upgrade as the ProClarity will be supported for another ten years.
So a bit different from the digital switchover, and the monitoring and planning stuff you get with Performance Point are a lot more useful than the shopping channels on freeview.
Full details and FAQ's are on the performance point site. Failing that contact me and I will try my best to answer your questions.
Following the resounding success of Jame's and Steve's virtualisation road shows the IT Pro Evangelist's are now taking bookings for the next tour and the details are here.
Viral and I are also on tour, which means we can expand our repertoire to wow you with Enterprise Search, Performance Point and SQL Server 2008. Our aim is to keep it real (even when showing virtualisation) so demos are in and we are all competing to see who can get away with the fewest slides.
It was a sellout last time so book early to avoid disappointment.