I see a lot of people asking about how to make integration services highly available and there isn’t an easy answer, so let me explain why.
Integration services (SSIS) is designed to bulk move a set of data from a source to a target while performing some sort of translation on the way. The most often causes of failure for SSIS packages I have seen are, in order:
1. The source is not available. This can because a process upstream of the SSIS package has not completed to create the target, a change in credentials or an actual issue with the source 2. The target is not available. Credentials or networking issues are the main culprits 3. There has been some change to the structure of the source or the data in it , not anticipated in the design of the package.
1. The source is not available. This can because a process upstream of the SSIS package has not completed to create the target, a change in credentials or an actual issue with the source
2. The target is not available. Credentials or networking issues are the main culprits
3. There has been some change to the structure of the source or the data in it , not anticipated in the design of the package.
No SSIS high availability solution is going to be able to mitigate these risks, so you end up protecting what you can by making the source and targets highly available and then try and figure out some way of doing the same for SSIS which doesn’t have any inherent high availability built in e.g. it is not cluster aware.
If this is something you do want to do then I would run SSIS on two separate servers and put tests and logging in the packages such that if a package runs on server B it checks to see it has not already been run on server A. In this scenario it’s important to ensure each package can be rerun from the beginning without corrupting any data (something which is good practice anyway). It is also possible to set up load balancing for SSIS(http://technet.microsoft.com/en-us/library/ms345184.aspx), and store logging information centrally (which could be on a cluster).
Another thing to consider if this flow of data is mission critical and not too large is to use a completely different approach like BizTalk.
Activity Monitor is a simple and quick way to see what’s happening right now in SQL Server,
Not too much happening on my demo rig but you get the idea.
It took me a few minutes to open this because my local books on line has an error in it on how to open it (it suggests expanding form the management tree) so my other tip is to use the books on line that is actually on-line.
Another good thing that’s done at the SQL Server community evenings is a 5 minute slot where anyone can have a go at showing something interesting.
Tony Rogerson got me to open the batting to encourage others to have a go. I am not sure who it was who came up next but it was a simple thing on renaming the infamous sa account…
ALTER LOGIN sa WITH NAME = SECRETSA.
ALTER LOGIN sa WITH NAME = SECRETSA.
Tony was too keen on leaving his laptop like that in case anything broke so just run the same command again..
ALTER LOGIN SECRETSA WITH NAME = SA.
ALTER LOGIN SECRETSA WITH NAME = SA.
It’s a useful security tip, especially if you are seeing lots of failed sa logins.
Among the interesting stuff discussed at last nights SQL community meeting at the Microsoft Campus,was a lively debate on whether to wait for SQL Server 2008 sp1. The traditional view has been to wait until sp1 because that is the stable release. One wit in the audience suggested that SQL Server 2008 was really SQL Server 2005 sp4 and therefore everyone should go right ahead. While SQL Server 2008 is actually a lot more than a service pack, it is not the major rework that SQL Server 2005 was.
David Portas an MVP working for Conchango pointed out how stable it was and he should know he’s been using it for year. He went on to pint out that maybe an sp1 would not be that well tested and would you really deploy that version the day it came out?
My own take on this is that there probably won’t be a service pack for a long time if at all. SQL Server 2008 is very stable and don’t take my word for it because if it wasn’t the stories would be all over the web (the Register, ZDNet, slashdot etc). So your decision on when to upgrade should not be influenced by that and reasons to upgrade should instead depend on what you are doing with SQL Server:
If your interested in SQL Server 2008, you might want to download the product from your TechNet subscription and at least install the client on your local machine so you can see how it looks.
It’s a simple matter to restore SQL Server backups to later versions, in fact you don’t need to do anything different but what about the other way around?
I first got caught out with this back in the days of SQL Server 7 and 2000, and the basic answer is the same today for SQL Server 2008 as it was then – you can’t restore a backup from a newer version of SQL Server to an older one. Attach and detach will also fail.
The simplest approach I can think of is to transfer database objects in Integration Services. And don’t forget to bring over any logins as well to avoid orphaned users for example:
Bcp master..syslogins out \\sqlserverdemo\data\syslogons.dat -N -S . -T
Bcp master..syslogins out \\sqlserverdemo\data\syslogons.dat -N -S . -T
EXEC sp_resolve_logins @dest_db= ‘personnel’ @dest_path= ‘\\sqlserverdemo\data\’ @filename= ‘syslogins.dat’
EXEC sp_resolve_logins @dest_db= ‘personnel’
The only reason I can think of that you would need to do this would be to back out of a migration to a later version of SQL Server, and my advice is to do everything possible to avoid the need for this to happen by carefully planning the migration.
I am still getting loads of questions on virtualising SQL Server so rather than blogging away here, I am getting involved with Windows ITPro magazine along with a number of cross industry virtualisation experts to run a virtual (what else would it be!) event “Virtualization: Get The Facts” from 11:00am (GMT) tomorrow.
I am on the SQL server slot at 15:00 so if I haven’t answered your specific questions you can virtually chat to me from the comfort of your desk, and we can both save the cost and time of travelling.
If you need any other incentive you can win an ipod nano (??) for joining in!
The other hot topic at last Friday’s ICAEW round table on Business Intelligence was KPI’s.
Toby Wilson (Finance Director of Microsoft UK) explained that Microsoft uses 30 KPIs despite the diversity of its diverse business (XBox, Mobile, Live services, Software, Consulting etc.). These are set top down and although they are a constraint they are fair because everyone knows the rules of the game. Once you get green on so many of these (including revenue of course) then an FD has flexibility to innovate to be more successful.
One of the other finance directors on the panel countered this by stating that there was only one KPI needed by a business – cash. His assertion was that many a profitable business has gone to the wall because it actually had no money, and in the current credit crisis this is even more likely.
However the sorts of KPI’s used at Microsoft and many other companies I have worked for are lead indicators and monitoring these will lead to sustainable long term growth. This is where the balance in balanced scorecard comes in. In this case balance between success today and success tomorrow.
A typical balanced scorecard might look like this with KPI’s under each heading
The other key facet of the balanced scorecard and its associated KPI’s is to derive departmental, team and individual scorecards from the top level scorecard for the company and to link individual bonuses and performance to these.
This approach was worked in numerous companies from the global Microsoft sized organisation down to small businesses with under 50 employees, since the theory was first published by Robert S. Kaplan and David P. Norton in 1992. To conclude I am sure that the focus it provides to all staff in an organisation is even more important today than it was then, but it is also important to never forget about financila health whether your own or the company you work for.
As I have remarked before Business isn’t run by the IT Department it is run by the people who hire and fire the IT department i.e. the Finance Director (FD). Given the current economic climate now would be a perfect time to see what you can do to help, so I have spent the morning listening to my FD (.. of Microsoft UK), Toby Wilson at the Institute of Chartered Accountants of England & Wales (ICAEW). He joined a round table on Business Intelligence hosted by Andrew Sawers the Editor of Financial Director. I thought some of the discussions although often repeated would be worth repeating because these are straight from the men with the cheque books, and their number one priority is:
One Version of the Truth. Often repeated mantra perhaps because it is so rare in many organisations. Toby deals with this in a two key ways:
One really obvious point is the need to snapshot your data at point in time so all reports from it are based on the same set of data. Several technical approaches can help to achieve this
My other top tip is to ruthlessly ensure that every reports contains a header with the all the details of how filters are set the date it was published and by whom etc.
Data Visualisation is one of my many interests and Marc Holmes showed us a bunch of wordles like the one above in yesterdays team meeting, and we had to guess the blogger. Mike Taulty won despite not recognising his own blog!. You can see why Marc didn’t use my wordle, SQL Server sort of stands out from the rest.
So what you see is what you get!
So here’s how it’s done..I rarely know more about SQL Server than my friend Beatrice so I was surprised when I mentioned plan guides and she hadn’t heard of this new feature. I have to say this is not a big mainstream thing and it takes me back query hints in SQL Server 2000.
Essentially you get the optimiser to generate a plan for you and then apply this to the query so that it always gets used every time that query is subsequently run. It might be a good way of getting the best performance out of a third party application you can’t change.
So here’s how it’s done..
SET STATISTICS XML ON GO SELECT WorkOrderID, p.Name, OrderQty, DueDate FROM Production.WorkOrder AS w INNER JOIN Production.Product AS p ON w.ProductID = p.ProductID WHERE p.ProductSubcategoryID > 4 ORDER BY p.Name, DueDate; GO SET STATISTICS XML OFF GO
The important bit is the query plan (double click on the xml to see this)..
You can get the SQL handle for the plan like this …
SELECT * FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp WHERE qt.text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%'; GO
and create a plan from it with this…
DECLARE @plan_handle varbinary(64); DECLARE @offset int;
SELECT @plan_handle = qs.plan_handle, @offset = qs.statement_start_offset FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp WHERE qt.text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
EXECUTE sp_create_plan_guide_from_handle @name = N'MyPlanGuide', @plan_handle = @plan_handle, @statement_start_offset = @offset; GO
There are event classes to see if its being used or missed e.g. you might have changed the schema for example and these are cunningly named as
You can check your plan guides using fn_validate_plan_guide(plan_guide_id), for example
USE AdventureWorks; GO SELECT plan_guide_id, msgnum, severity, state, message FROM sys.plan_guides CROSS APPLY fn_validate_plan_guide(plan_guide_id); GO
You can see if a plan guide is in use if you run the query again and look at the properties of the query plan (press F4):
The plan guide will also show up under programmability in Management Studio:
and to get rid of it you’ll need to run
EXEC sp_control_plan_guide @operation = N'DROP', @name = N'[MyPlanGuide]'
Finally the full detail is here in the SQL Server TechCenter
I rarely go off topic on my blog, but I was wondering whether I am wasting my time here. Eileen rhas some research predicts that blogging is becoming redundant and the new thing to do is microblog on Twitter (I am DeepFat on Twitter) , Yammer et al, as this is the best way to share things instantly. My research indicates that my stuff is being read more and more and I get a lot of positive feedback when I get to meet some of you. I do use microblogging to keep in touch with what I am doing, but not how to do stuff or why.
Just to be clear this blog is not about level 400 deep dives in to the dark corners of SQL Server. It’s about:
I don’t see the point in competing with the excellent technical output from MVPs and the wider UK SQL community, or with all of the essential stuff in TechNet. When I call out important KB’s, Hand on Labs, WebCasts and events, it’s because these are in my opinion the good ones.
Another point is that I find blogs generally easy to search and I use mine as my personal books on line. Some of the stuff on here requires considerable research, as I haven’t always got the answer, so if I am wasting my time here I can free up about 20% of my day for other things.
My final point is that blogs are quite permanent which is both good and bad. Good because when SQL Server 2008 is in extended support some of this will be useful to those businesses that haven’t upgraded yet. Bad because if I make a mistake it’s there until it’s discovered (so far only the odd typo).
OK Rant over, let me know what you think while I get back to SQL Server.
Imagine you have a windows 2003/SQL Server 2005 cluster (active-active) and your mission is to both virtualise and maintain high availability using Hyper-V in Windows Server 2008/SQL Server 2008. This may sound odd but I was asked the question at TechEd and I wanted to do some research on it. Fortunately I on Windows 7 training this weekend and have access to 2 of the key players Jeff Woolsey who ‘owns’ Hyper-V development and clustering product manager Dave Dion.
So are your options? and more importantly how do they help?
I would start this by addressing what are you most worried about…
Also what are your goals e.g.
Finally other workloads are also on this setup which will also be virtualised.
The customer who asked me this wanted to cluster the cluster by..
So how does this stack up against our criteria above?
Basically not very good..
Initially this solution looks quite good - if node 1 or node 2 dies then the surviving vm on the surviving node will take ownership of the storage. But this will take time. Currently hyper-V VM’s only support iSCSI (fibre channel is on the way). The two VMs will have different routes to the same shared storage so if that is not accessible then the system will fail. However the killer is that Microsoft do not support SQL Server failover clustering of VMs as per this KB. BTW this limitation applies to all virtualisation technologies, not just Hyper-V.
Is there a better way? For example if you mirrored the critical databases between vmA and vmB then you now have a copy of the database on either shared or separate storage. You could then script a manual failover to the mirror if the node running on the principal moved over to the other node as its storage would now be remote to the VM. This is supported, and mitigates a number of failures including:
Of course this is at the database level, but the principal could be extended for more than one database and other workloads as well.
However in my opinion the best way to consolidate here is to create a physical cluster and consolidate SQL Server workloads on into it. Clustering is a lot easier in Windows Server 2008 and you n o longer need carefully matched hardware. though the surviving nodes in a cluster need to be powerful enough to handle all of the work of the cluster.
Let me know what you think and what your plans are in this space.
I have finally finished at TechEd after another round of interesting questions on the Microsoft SQL Server ask the experts stand. Virtualisation was the consistent hot topic and the 2 FAQ’s are:
Is it supported? Yes fully in a variety of situations the chapter on verse for Hyper-V is here. Apparently there are other virtualisation platforms out there and so there is also a Server Virtualisation Validation Program (SVVP) which also means that Microsoft will also support SQL Server (and the rest of the Microsoft server) on a variety of other Virtualisation platforms including selected products from:
Photo’s courtesy of Kevin Moran
I often say that a database is only as good as the system it is running on. For example SQL Server supports 64 cores (as opposed to CPUs) because that is the limit in Windows Server, even in Windows Server 2008. However if you have been at PDC or TechEd in Barcelona (where I am writing this from) then you will have seen that Windows 2008 r2 will have support for 256 cores. Surprise surprise the SQL server team at WinHEC (the Hardware Engineering Conference) have now issued a statement that SQL Server can use all of that with the release Kilimajaro.
If you haven’t heard of Kilimanjaro it’s primarily aimed at providing end user business intelligence tools for SQL Server.
BTW SQL Server is licensed per CPU so this could be a really cheap solution if Intel release a 256 core version of the Xeon.
During a quiet moment at TechEd I was talking to the Search Server Express product team about our respective technologies (SQL Server and SharePoint) and how significant they were. I have a simple demo to prove how widespread SQL Serve is to back me up in these situations which you might like to use if you are feeling unloved and unwanted at work…
I opened the data management track at TechEd EMEA yesterday, to a full room and as ever if I am unsure of a question I research it and put it on this post the next day.
I was pretty sure of the differences in the way database mirroring in standard and enterprise edition of SQL Server 2008 which are:
Standard Edition you can only use mirroring in full safety mode i.e. synchronous mirroring optionally with a witness to enable automatic failover. btw the witness can be express, but the mirror must be standard as well.
There are no restrictions on how many mirrored databases can be setup on the mirror in standard or enterprise (which could come from different principal servers), however there are some things to remember:
If you are TechEd I have now got the shuttle working like this
… so that the database in the cluster is mirrored to another server which in turn is log shipped to another instance, so come and say hello on the SQL Server Ask the Experts stand.