You don’t have to install all of the full SQL Server client tools to get connectivity to SQL Server or analysis services. They have been broken up and are available individually for download as part of the SQL Server feature pack.
For example you can pull down:
X86 Package (sqlncli.msi) - 4549 KB X64 Package (sqlncli.msi) - 7963 KB IA64 Package (sqlncli.msi) - 11112 KB
X86 Package(SQLServer2008_ASOLEDB10.msi) - 19490 KB X64 Package (SQLServer2008_ASOLEDB10.msi) - 43945 KB IA64 Package(SQLServer2008_ASOLEDB10.msi) - 50682 KB
X86 Package(SQLSERVER2008_ASAMO10.msi) - 2659 KB X64 Package (SQLSERVER2008_ASAMO10.msi) - 4317 KB IA64 Package(SQLSERVER2008_ASAMO10.msi) - 6055 KB
There’s also a load of other utilities like backward compatibility stuff e.g. to edit and run data transformation services packages.
Finally as I have blogged before these tools are not licensed in themselves, it is SQL Server that is licensed for either client access or per cpu (socket NOT core).
Many resources on data warehousing talk about slowly changing dimensions and how to deal with them but what happens when your dimensions change more quickly and what is does fast or quick mean in in this context?
First let’s be clear on what is meant by slowly changing dimensions. Any change to an attribute in a dimension typically happens far less often than changes are made to fact tables. Think of changing product specifications, customer moving address, and organisational change in a business, all of these happen much more rarely than facts are added or changed in a fact table.
However there are some changes that happen more quickly than others, for example the current stage of a project or process could change daily or even hourly, as could the status of a customer complaint.
Then there is the matter of how changes to dimensions should be handled. If history is to be discarded and only the current view of the project etc. is to be kept then this is referred to as type 1 ( check my post here for more on this). This entails doing an update which can be really slow especially if there are lots of them. If history is to be kept then (Type 2) the processing is more complex and you end writing a row in for each time a dimension changes. So if your dimensions are changing a lot then the refresh process is going to be very slow and your dimension are going to be very large.
What can be done to stop this from happening? My recommendation would be to change the design by:
1. Taking out the fast changing attribute (for example project status) and creating a dimension with all of the possible values in.
2. Creating a factless fact table to record the changes with the following attributes
What we have lost in the process of doing this is a complete readout of the project in one simple table which makes the job of saying what is the current stage of my project a bit harder to find out. Also if project status is not null (and in my designs no dimension attributes are allowed to be null), then you need to create an initial row in the new fact table every time you create a new project to point to the initial status of the project.
Hopefully this is useful as there isn’t too much on TechNet about designing a data warehouse. If you do want to know more check the Microsoft data warehousing books in my book list by Ralph Kimball, as this is pretty much what all of the tools in SQL Server are designed around (especially integration and reporting services).
Microsoft reckon that 7% of workloads are virtualised, so there’s still a lot of consolidation work to be done, but how high up the list should SQL Server Analysis Services (SSAS) be?
In order to answer this it’s important to understand what SSAS is there to do, which is to provide a high performance ad hoc query environment for quantative analysis. To do this effectively SSAS needs RAM and CPU as well as good connectivity, so in many BI projects analysis services is installed on a dedicated server specifically tuned for the task.
So it is then important to understand what the motive is for Virtualisation:
a. We need to consolidate our server workloads b. We want to leverage the ease of management that virtualisation can bring, by being able to bring up, roll back and turn off workloads. c. It’s new and shiny and everyone is doing it, so it must be good.
a. We need to consolidate our server workloads
b. We want to leverage the ease of management that virtualisation can bring, by being able to bring up, roll back and turn off workloads.
c. It’s new and shiny and everyone is doing it, so it must be good.
Applying this to analysis services
a. If your cubes are of any size or are serving a large user community then you aren’t going to consolidate. Or you have a smaller business and you already have all the SQL Server features on one box to reduce licenses and fully utilise a single server. Either way Virtualisation isn’t really going to help. b. Updates and patching and switching can bring benefits to SSAS like any platform. c. You will ignore this post and press on anyway.
a. If your cubes are of any size or are serving a large user community then you aren’t going to consolidate. Or you have a smaller business and you already have all the SQL Server features on one box to reduce licenses and fully utilise a single server. Either way Virtualisation isn’t really going to help.
b. Updates and patching and switching can bring benefits to SSAS like any platform.
c. You will ignore this post and press on anyway.
I haven’t mentioned performance yet and I don’t believe there are any definitive figures on running SSAS on Hyper-V or any other virtualisation platform, but some customers have noticed problems running SSAS in a virtual environment, more around concurrency than response times.
So my question is do the benefits outweigh the disadvantages? In my opinion virtualisation of SSAS or indeed any OLAP using any virtualisation technology should be right at the end of your list of workloads to virtualise. Firstly Virtualisation technology is moving on all the time, secondly as more and more people try this there will be more best practice around.
Having possibly put you off if you are considering doing this using the Microsoft platform (i.e. SSAS over Hyper-V) then the SQL Customer Advisory Team Team (SQLCAT) would love to hear from you so please contact me or them on there blog.
I have to say when I got to discussing SQL Server Data Services as was with James Rowland Jones at SQL Bits cubed it left me a bit cold. I am not a web developer I guess because I am too old, but while the ACE (Authority, Containers and Entities and Flexible Entities) model sounded attractive it looked like back to the drawing board if you wanted to port your application from SQL Server in your data centre to the cloud.
That has changed and SDS will now support SQL (T-SQL) and understand transactions like any relational database. For a simple soul like me it seems that this is for the better - If I have written a load of code to work with SQL Server on my phone/ laptop/server shouldn’t I also be able to also opt to deploy that to the cloud, without having to rewrite it?
This announcement was only made yesterday and the only public details I could find are on the SDS blog. So many of the other resources on MSDN and the SQL Server micro-site have yet to be changed, and I need to get hold of James to reshoot it hopefully at the next SQL Bits!
My good friend Graeme Scott, while being an excellent presenter is not a blogger, but he has had a lot of requests to share his SQL Server session from the Microsoft Application Platform Update on 6th March and I am here to help.
So if you caught his session last week here’s the link to his deck, and if you didn’t it’s a good introduction to the new features from a developer perspective.
Viral has just pinged me a write up of a hacking contest from the Washington Post (he is American after all) . The "Pwn2Own" contest at the CanSecWest security conference in Vancouver won by a 25 year German student called “Nils”. He won $15,000 for exposing a vulnerability in IE8 beta. This is good as this is what betas are there for and this was fixed the next day by Microsoft (as discussed here on the Microsoft Security Research & defence blog) and so the released version is that but more secure.
However browsers run on operating systems so what’s also interesting in this article is how the cross platform browser Firefox is more secure on Windows Vista / Windows 7 of their data execution prevention (DEP) and address space layout randomization (ASLR) capabilities. XP has DEP from sp2 but ASLR only came in with Vista and to quote Nils “ASLR doesn't appear to be properly implemented between OS X and versions of Safari and Firefox built for that operating system”.
Finally it is only with IE8 that the browser can block the .NET DEP +ASLR bypass mechanism and so as Nels says in the article
"It's getting pretty hard to do a lot of this stuff on Windows Vista and Windows 7”
I just wanted to make sure that anyone watching my TechNet webcast last night has all the resources they need, but before I do that I also wanted make the point that Upgrade is a simple task for many environments provided you follow the advice in the resources below:
Finally if you want really cover your back, then my top tip is to use the support calls included your TechNet + subscription (you get 2 each year). Please get back to me if you haven’t got one already.
Speaking at SQL Bits is a cool thing to do but may terrify many of you. Part of this is that you are on stage for an hour and that means a lot of preparation, both content and rehearsal. So one of the organisers, Martin Bell has come up with a competition to gradually introduce the idea of speaking to you - Five Minutes of Fame.
You only need to present one simple tip or how to, you’ve got five minutes and there’ll be laptops there for you to use.
So if you aren’t coming along why not (SQL Bits is on 28th March at the Manchester Metropolitan University)? and if you are already registered why not have a go at this?
By a strange quirk of fate I have not really had to do too much with CLR functions in SQL Server until I started hanging out on Twitter. Then I saw this question..
“How do I remove non-numeric characters from a VARCHAR?”
Easy I thought until I realised that T-SQL isn’t really that good with string functions. Anyway I have cracked it but it isn’t going to be fast against a huge table, because I have a loop inside a scalar function ..
ALTER FUNCTION production.ufnChartoNumeric ( @String nvarchar(50) ) RETURNS int AS BEGIN DECLARE @CleanNumber int -- the final product DECLARE @CleanString nvarchar(50) ='' -- the inputstring with only numbers in DECLARE @StringLength int = len(@String)+1 -- the length of the string +1 DECLARE @Loop int = 1 -- Loop counter -- If the string is already a number we can assign it to the result and finish IF isNumeric(@String) = 1 BEGIN SET @CleanString = @String END ELSE -- If itsn't a number we need to loop through the string a character at a time -- and test if each character is a number. -- if it is append it to our clean string if it isn't move on BEGIN WHILE @Loop < @StringLength -- loop for each character in the string BEGIN IF isNumeric(Substring(@String,@Loop,1)) = 1 SET @CleanString += Substring(@String,@Loop,1) SET @Loop += 1 END END -- if there's a decimal point and you want to keep it, (but which one if there are 2!) -- you'll need more code to detect it -- and maybe return a float, but the first test will keep it -- so for this example I want to eliminate it. SET @CleanString = Replace(@CleanString, '-','') SET @CleanString = Replace(@CleanString, '+','') SET @CleanString = Replace(@CleanString,'.','') SET @CleanNumber = Cast(@CleanString as int) RETURN @CleanNumber END GO
As you can see I also had to deal with decimal points and plus and minus signs as these could occur many times in a string. BTW there are little bits of T-SQL 2008 in here such as ‘+=’ and DECLARE .. = .. which you’ll need to change for older versions.
It does at least work, (I tested it against a few random string and against production.product.productnumber in AdventureWorks), but the only way I can think of to improve the speed is to rewrite it as a CLR which is much better at handling in row string manipulation.
But I leave that for another day as I am not sure what this is exactly needed for, and often something that works now is better than something fast tomorrow.
Following on from my last post, Microsoft doesn’t have a site where you can rate our performance on an individual basis, instead it relies on surveys. One example of this is that you will on occasion be asked to fill out an online survey when you access a Microsoft site.
The most important of these is an overall customer satisfaction survey (known internally as the Global Relationship Survey) carried out every six months and sent to over 3.3 million people worldwide. Recipients are chosen at random from carefully selected categories such as IT professionals, IT managers and developers in each country across various sizes and sectors of business by a third party company specialising in this.
This is a huge and expensive exercise, so why bother? You might think this is because customer satisfaction is a lead indicator,Microsoft can use this data to predict market share and revenue. But that could be done a lot cheaper than this as the survey has many questions in it on detailed aspects of your relationship with respect to support, account management, the products themselves to name just a few.
The real reason this survey is there is to tune and improve what is working and to address the concerns raised. So if you get one of these all I would ask is that you complete it to give us you honest and open feedback.
In pretty well any service industry you can leave feedback about your experience, even Microsoft will pester you to fill in evaluation forms whenever you attend one of our events. However while the NHS often seems to be awash with scorecards and league tables, feedback from its customers is usually limited to the occasional focus groups.
Then the BBC picked this announcement up from Gordon Brown on 10 March -
“We are ushering in a new world of accountability in which parents, patients and local communities shape the services they receive”
Possibly this was this prompted by the I want Great Care web site setup by Dr Neil Bacon to allow patients to record their feedback on doctors..
The worst that can happen with following feedback advice on Ebay or Trip Advisor is that you stay in a crummy hotel or you didn’t get the bargain you were expecting, but with the I Want Great Care site, peoples’ health and doctors’ careers are at stake so there have to be much tighter controls to ensure the quality of the data and that the reviews are really genuine. Dr Bacon is passionate about good patient feedback so he already has a number of controls to mitigate against this with more on the way.
As you can imagine that this has created a lot of controversy and interest (for the case for and against check the Times OnLine, and yesterday Sunday Telegraph), but it has also attracted interest from a number of charities including Diabetes UK and the Stroke Association who want to see how these illnesses are being treated.
Why am I mentioning this? Because Microsoft helped to make this happen with BizSpark, a complete support service for startups. In the case of I Want Great Care, this was the provision of the back end technology and the hosting of a Proof of Concept on 6th March attended by senior NHS officials; Sir Muir Gray, chief knowledge officer of the NHS, Sir Cyril Chantler, chair of The King's Fund and Sir Donald Irvine, chair of Picker Europe, as well as PCT and acute trust directors.
The site is now out there and even if you don’t use it yourself, it might inspire some thought around customer feedback in your industry, or to think about putting that web 3.0 idea into reality.
Security is a thankless task, your best work goes unnoticed and if you let down your guard for a minute you can be front page news, like the Daily Telegraph (interestingly on the Guardian web site). In this case the method of attack is old chestnut, SQL Injection which dates back to 1998.
As a DBA there isn’t been much you can do to stop this as it is the code in the application run by an authenticated user that is doing the work. There are lots of tools and advice to help developers write secure code which include the following from Microsoft:
As a DBA you can at least track what’s going on to help mitigate future attacks of this type. In SQL Server 2008 Enterprise edition you can use the audit feature which is much easier to enable than setting up trace and takes less overhead. It can also if necessary output to the application or security log as well to to file for later analysis. For more on audit check this TechNet Article by the SQL Customer Advisory Team (SQLCAT).
This is just one area where database are under threat and so I will continue this theme throughout the week to cover some of the other things you can do to keep your data secure (and possibly your careers as well!).
The possibilities for consolidating SQL Server are many and varied, and one of the key drivers for all of these is the cost of energy which curiously doesn’t seem to have fallen in line with the price of oil & gas.
Anyway I found this detailed whitepaper on how Microsoft applies the principles of sustainable IT as applied to SQL Server to its data centres. The numbers in this document are staggering - 5,000 instances of SQL Server providing over 100,000 databases, but what is more interesting is the drive to virtualise a lot of this while recognising that there will be some workloads more suitable than others.
There’s also a lot of tips that could well apply to other enterprises:
Most of this has come form the SQL CAT paper on best practices for running SQL Server on Hyper-V, so Microsoft does actually follow its own advice!
It’s time to spring clean the blog as I have realised that a few of my older posts have been overtaken by events and could be misleading if they were acted upon now.
For example:
If you spot anything else please let me know, as IT is confusing enough without me inadvertently adding to it.
I have decided to add security to my blogging activities so here goes..
Microsoft has developed a range of products under its Forefront brand which add security into some of it’s more connected products. In an ideal world these would be built in to each part of the platform, however there is a huge industry in providing security software and if Microsoft started bundling these capabilities into its products there would inevitably be cries of foul, monopoly and anti-trust.
So what are these security add-ins and what are they protecting?
Updated 25 March: One of my Twitter followers @Bibbleq has quite rightly pointed out that FSOCS was released on 16 March 2009, sorry about that, Andrew
These products are all individually licensed, and are also included in the Forefront Security Suite or the Enterprise CAL Suite. I have called out the links to evaluation copies above but all of this is available in non time-bombed form via your TechNet subscription.
1st of April could well see the resurgence of the infamous Conflicker or downadup worm which some experts reckoned had infected over 3.5 million computers by January. This is because Conflicker will use a new algorithm to work out which domains to attack.
Many of the problems that have been reported were simply because so many systems were not kept up to date, the critical security update being MS08-67 released on 23rd October 2008. So if you want a quiet relaxing time this week can I suggest that you check the following resources and ensure your systems are in compliance with the advice in them:
There is also advice on the page on how to clean up the mess if you are too late, and the various variants of Conflicker and what to do about them.
It might also be good to let your friends and family know about this so that you can also relax when you get home and the resources they need are here.
Microsoft has this 3 pronged approach to ensuring your infrastructure is secure, but you have to do stuff as well. This 3D security goes like this:
Secure by Design. According to Wikipedia, “means that the software has been designed from the ground up to be secure. Malicious practices are taken for granted and care is taken to minimize impact when a security vulnerability is discovered or on invalid user input.
Secure by Default. Anyone installing SQL Server 2005/8 for the first time will know that having done that, there are further tasks needed to get access to the environment remotely as all the connectivity options are turned off. This gives the DBA the opportunity to configure security before everybody turns up to use it.
Secure by Deployment. This is where Microsoft and some other software suppliers, provide resources to ensure that your systems continue to be secure. This can take several forms:
This last section is all about trust, You trust your Microsoft or whatever vendor to be open and honest about security issues it has detected and to develop the appropriate resolution. Your vendor and your users need to trust you to implement the advice and resources that have been provided. I mention this because I noticed this ComputerWorld Security article that cites most (+70%) of Oracle DBA’s are not keeping up with applying patches to their systems. This isn’t any criticism of Oracle rather it’s illustrative. However I am hoping I am evangelising to the converted to the SQL Server DBA’s here!