Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

August, 2008

  • SQL Server Migration

    I knew that there was a toolkit to help move an application from Oracle to SQL Server, but I hadn’t appreciated that there was one for Sybase and Access these also now come in two flavours, one to move you to SQL Server 2005, and one for SQL Server 2008:

    SQL Server 2005 Migration Assistant for:

    SQL Server 2008 Migration Assistant for:

    Before you ask, there aren’t any assistants fromMicrosoft to do migrations to these platforms from SQL Server,  although I am sure that Oracle and Sybase will be happy to help with that.  What I can’t understand is why anyone would go this way, so post some comments and enlighten me.  

  • Career Limiting Move, SQL Server 2008 Transparent Data Encryption

    Transparent data encryption (TDE) in SQL Server 2008 enterprise edition is a great tool for protecting your data ‘at rest’ , but you need to be careful when you use it.

    TDE only really comes into play when you move the database to another location (hence the transparent in the name). When you encrypt a database you do so by making a key and using that. If you move the database to another location e.g. with detach-> copy -> attach or backup –> restore then you must have that key at the new location.   Bear in mind that  a new location could just be the original server which has had to be totally rebuilt as the key won’t be there anymore. 

    Here’s an example of how to use TDE in a career limiting situation:

    DBA: I need to protect my data from unauthorised access

    Microsoft chap: Well, you could use TDE this cool new feature in SQL Server 2008

    DBA: Thanks Microsoft bloke

    time passes..



    DBA: Hey Microsoft chap I used that TDE thing but the server crashed and I can’t get my backup to restore.

    Microsoft chap: Ah OK you need to restore the key you used to encrypt the database and her’s some sample code:

    -- if there is no master key in the master database, create one (don’t do this if there is already one existing)

    use master


    -- restore the certificate including private key

    CREATE CERTIFICATE MyDBCert FROM FILE = 'c:\SQLBackup\MyDBCert.cer'     WITH PRIVATE KEY ( FILE = 'c:\SQLBackup\MyDBCert.pvk',

    -- You need the password used to backup the key

            DECRYPTION BY PASSWORD = 'P4ssw0rd Us3D 2 BKup Th3 KEy' )

    -- now you can restore the database

    DBA: Where do I get that private key? on TechNet?

    Microsoft chap: I ‘m afraid we can’t really help unless you have the key that you used to originally encrypt the database.

    DBA: Surely you have a workaround, backdoor thingy?

    Microsoft chap: Afraid not, because that would mean that we could look at the very data you were trying to protect from unauthorised access.

    exit ex-DBA

    If you are using TDE the first thin you should do once you have made your key is backup the key like this

    BACKUP CERTIFICATE MyDBCert TO FILE = 'c:\sqlbackup\MyDBCert.cer'


          ( FILE = 'c:\sqlbackup\MyDBCert.pvk',

            ENCRYPTION BY PASSWORD = 'P4ssw0rd Us3D 2 BKup Th3 KEy'


    Use the key to encrypt a copy of your database and try to use the key to restore it to another location (which could just be another instance on your test server). Once you have got your head round that then you are ready to try it on the production database.

    Remember to keep that key safe, like you would your backups, but NOT in the same location as that will again mean that your use of TDE is pointless as anyone with the key and the data has your data.

  • Analysis Services Backup

    Backing up a cube is not an ideal experience in SQL Server 2005 for two reasons, it’s a manual process to schedule a backup and as the size of the cube grows the backup time increases exponentially i.e double the size of the cube and the backup time and size will increase by a factor of four.

    Curiously although there is excellent management tools support for managing SQL Server as I mentioned yesterday, I am not aware of similar support for analysis services, despite the many third party tool vendors who make front ends for it. so here’s a few things to help..

    To automate  a cube backup use the SQL Server Management Console to  generate a script, by selecting the analysis services database to be backed up  right click select backup and then click on the script icon as shown below:


    The generated script will look like this:

    <Backup xmlns="">


        <DatabaseID>Adventure Works DW 2008</DatabaseID>


      <File>Adventure Works DW 2008.abf</File>



    .. depending on the options you set  (like checking allow file overwrite).  Anyway copy the script to the clipboard and then create a SQL Agent job to run that script.  the job step should then look like this once you have pasted the script.


    You will of course want to test the job and verify the cube can be restored.  You can get more info on all of this here.

    For really large cubes you may need to go for the raw backup solution mentioned in this article, but the other cure for the backup explosion problem is to upgrade Analysis Services 2008 as the size and time taken to do backups is a linear progression i.e. doubling the size of the cube requires double the space and double the time as you’d expect. 

    Moving a cube from analysis services 2005 to 2008 is about the most painless upgrade you can do and you can do this with a backup/restore or it will just get upgraded if you are doing an in place migration. 

    Technorati Tags: ,

  • 64 bit odbc connectivity to SQL Server

    Working for Microsoft is strangely like working for the government, yu are essentially trying to please everyone, but there are significant minorities on any given issue who aren’t that satisfied.

    Let me explain with a real world example..

    The lack of 64 bit jet and odbc drivers mean that anyone trying to get data into 64 bit SQL Server from Excel and Access has to do all sorts of chicanery.  Clearly those affected feel this is a big issue - after all this is simply a case of moving data from one Microsoft product to another. Virtually every Business Intelligence project is affected by this as there is always some niche data in Excel or Access that is needed in the data warehouse and typically these projects rely on 64 bit SQL Server.

    So why is this issue still hanging around? The answer is democracy, there have been more people asking the office and SQL Server teams for other features than for this one. Eventually this issues will be a high enough priority to make it worthwhile to do and that may well happen in Office 14 (but I am making no promises here). 

    Where Microsoft differs from Government is that you can often vote on a given issue rather than just vote in a party and leave them to get on with it. So if you are really unhappy about the lack of 64 bit odbc support then please please register your concerns on connect, and when you search for this issue as I did you realise that there a whopping 65 votes for it, last time it was raised over a year ago, and maybe thaa’s why the product teams haven’t picked up on it! 

  • SQL Server Databases and Cubes on one Server

    I have put Business Intelligence into a number of small businesses and these guys typically have one server set a side for BI, so how do you cram the database and the cubes into one box to get the best performance?

    SQL Server databases and Analysis Services (SSAS) both love memory, and even windows server gets upset if you deprive it of more than certain amount so here’s some guidelines:


    You can decide on different values based on your workload. Keep in mind that less memory will mean less pages in memory (more IO) on SQL and potentially less space for Analysis Services to cache queries. Processing will also take a hit depending on size since it copies the cube into memory for processing.

    • SQL Server Database - Set SQL to use max 40% physical memory
    • Windows - Set <LimitSystemFileCacheSizeMB> to 20% of total, 65022 MB * .20 = 13004 MB.  This limits Windows file system cache usage of memory.
    • Analysis Services- Set SSAS to use max 35% memory, edit the msmdsrv.ini file (make backup first) and set <TotalMemoryLimit>35

    Set <PreAllocate>35 (this means total percent of physical), to preallocate memory at server startup. This helps for NUMA enabled servers especially I found. More on SSAS properties can be found here and more tuning analysis Services can be found in this white paper.

    If there are problems with runaway queries using too much memory, you can run ASGovernor to cancel queries if memory usage is greater than 40%.  (5% past target for SSAS)  It might be that this is too large and it needs to have a smaller threshold. 

  • SQL Server 2008 Intellisense support for SQL 2000/5

    Intellisense for SQL, how hard is that?  Well judging for how long we have been asking for it the answer is “very”. Also talking to the product team it is actually much harder than for C# VB etc. because of the nature of SQL. 

    Anyway intellisense appeared in CTP5 of SQL Server 2008 and at that stage it had a few niggles in it which have now been fixed. One of those niggles was that it didn’t check which version of SQL Server it was running against, so you could use intellisense against SQL Server 2000/5 for example. However the downside was that it was directing you to write SQL Server 2008 compatible T-SQL, which would work most but not all of the time on the older versions.

    Rather than try and make intellisense work for the older versions, in RC0 and RTM it does now test for the version and is disabled if the target isn’t SQL Server 2008.  This isn’t going to please everyone as some people thought this was going to be implemented for all versions,  but it was never intended to support legacy versions, given how hard it was to develop.

  • Flying with Small Business and Essential Business Server

    Small Business Server (SBS) is like flying a single engined aircraft.  There’s a lot of stuff going on but you only have one box to worry about and backup and interoperability are all a lot easier because of this.

    Flying a twin engined aircraft is far more complicated than a single engined one; how come they don’t go round in circles when one engine doesn’t work? The new twin engined version of SBS is SBS 2008 Premium which adds power and flexibility to the base edition by adding a separate physical server to just run with Windows Server 2008 and SQL Server 2008 standard edition for you to run any line of business application, Microsoft or not, on top of that.  That power means the server is just a bit more complex to get working as it has to join the domain and security between this new server and the clients has to be configured.

    Moving up the complexity there is going to be a completely new server offering for mid-sized businesses called Essential Business Server (EBS), this comes in a basic three server version and a four server Premium version again with a separate SQL Server 2008 standard edition installation:



    The Management Server is the one that looks after the user accounts and from which you manage the other servers with a basic version of System Center. There is a copy of SQL Server 2005 Express on here for supporting System Center and you could use this for other small databases if you decide premium is not for you.



    The Messaging Server does what it says on the tin and so basically runs Exchange.




    The Security Server in the diagram above confusingly mentions Exchange as a component, but this is the gatekeeper for the whole system and so it has  the Exchange Server 2007 Edge Transport Role which provide for all the good stuff to stop spam and provides message hygeine.


    EBS will provide the back office IT for a company of up to 250 employees and that premium edition so that you can extend EBS to run Office SharePoint (SharePoint Services is still part of Windows Server 2008), Dynamics, or a thrid party application like Sage.

    At this point you are wondering what is all this doing on my blog.  It may well be a way small/medium businesses will get their first exposure to SQL Server and I wanted to help out with the launch of this as I am pretty impressed with how integrated everything is. To start this off I  will be running two TechNet events this autumn:

    If you can’t make either of those then two other top resources on EBS/SBS are:

  • Mini Computing Performance

    Performance Management doesn’t just apply to SQL Server, it is just as important in all aspects of engineering.  My cousin Stuart is one such engineer, he takes a standard Mini Cooper (the old kind) and adds a turbo and an Emerald engine control unit (ECU), plus a ton of other tech stuff to ensure the rest of the car can handle the upgrade.  Why not buy a new one? Well there’s a thriving market in the old ones, and all of this wizadry not only increases power it also drops emissions and means that the car is essentially recycled. Of course that has nothing to do with it, people just like the old one.

    And looking at this example on photosynth that he’s rebuilt you can see why..


    What’s all this got to do with computing? Just like any bit of technology performance tuning is important, and Stuart can pick up similar telemetry form the engine’s ECU..

    latest Windows based software

    and then tune the settings (known as maps) to get the engine to perform in a certain way. He does this from a laptop, which at the moment he needs to directly plug into the car, but he can log up to 4 hours of recording. 

    In SQL Server 2008 we are a lot better off, we have a way of capturing a lot of telemetry from multiple servers and putting all of this into one database called the performance warehouse.

    Stuart meanwhile would like to do this sort of thing and find a way of getting the info off the cars wirelessly or even totally remotely, so that he can provide better customer service. Perhaps that would like something like this from one of our partners Shoothill who have developed a virtual earth/silverlight application that track a hydrogen powered vehicle developed by Ballard Power Systems..


    Perhaps one day we will all be able to monitor our cars as well as SQL Server. It would be good for safety, vehicle theft, and the environment, but not so good for personal privacy if everyone can where you are all the time.

  • Licensing in a Virtual World

    Even if you work for Microsoft licensing can be complicated. I am pretty confident on what’s what with SQL Server, but I was in TV land yesterday at a large broadcaster, and they are looking to adopt Windows Server 2008 and SQL Server 2008 as a standard for all of their projects.  We spent an hour discussing the bits of SQL Server 2008 that would be of value to them and the issues they will have in upgrading and consolidating their current estate.  This then led onto virtualization and how Microsoft stuff is licensed in virtual machines.

    The first important thing to note is that Microsoft doesn’t care what the virtualization technology is as far as licensing is concerned.  Obviously Microsoft would be happier if you used Hyper-V but if you put SQL Server on whatever ware virtualization or Hyper-V the same licensing rules apply. 

    For enterprise businesses like a TV company you should be using enterprise edition on your servers for a whole range of reasons, but one of the key licensing benefits is that if you license the physical machine per CPU you aren’t constrained as to how many virtual SQL Server machines you can have on that box.  This frees you from pricing constraints when considering how to consolidate your servers. For example you could: 

    1. Stuff all of your databases into one instance, and use tools like resource governor to manage who wins when the server is under pressure.
    2. Create instances and assign memory and CPU to them so you have fixed resources available for each of them.
    3. Create as many SQL Server virtual machines as you can cram onto the physical machine.  Note you will have to license the operating system for each of these virtual machines as well as SQL Server.  In the same way that SQL Server enterprise edition doesn’t ‘care’ how many virtual machines there are Windows Server DataCenter edition doesn’t either. 

    If you want to know more about licensing and virtualization then you could do worse than watch this:

    and check out Emma Healey’s licensing blog for virtualization articles.

  • SQL Server 2008 Released

    sql2008 sig

    I hope you are sitting down, and don’t have a coffee in your hand when you read this, but SQL Server 2008 released at 17:00 GMT today!

    This is good news for all the people who have asked me about release dates as they can now start to plan and deploy in earnest. 

    It’s good for me as I can now start building the demo’s I need to for the next round of TechNet road shows, TechEd IT Forum, and even a quick demo for when Steve Ballmer pops over in a couple of months. 

    And it’s good news for you even if you aren’t one of the 500,000 people who downloaded the various betas and ctps, but in order for you to find out why then you could do worse than come to a SQL Server event this autumn such as:

    • Microsoft SQL Event 10th September  in Reading (not posted on the events site so pencil it in for now).
    • SQL Bits Cubed 13th September in Hatfield
    • SQL Server Under New Management TechNet Road show Manchester in 7th October (register here) and Exeter 23rd October (register here) and in Manchester.

    You might also want to download it and this can be done from the main SQL Server site

    Also don’t forget to get the feature pack which should also be updated for release. This has all the important add-ins like the upgrade advisor, command line utilities and best practice policies. 

    Technorati Tags: ,,
  • SQL Server 2008 Report Builder v2.0 RC1


    As I mentioned a few weeks ago the end user reporting tool for SQL Server 2008, Report Builder v2 didn’t release with SQL Server 2008. There was an RC0 version of it with SQL Server 2008 RC0 and when SQL Server 2008 released this download disappeared causing some confusion and scratching of heads. 

    The good news is that you can now download the RC1 version here.  The really good news is that it has got some wizards and loads of other stuff in it to make using it a lot more friendly.  It’s also a standalone .msi which you can then deploy to your users as needed, although I believe it will eventually be a click once application like Report Builder v1 in SQL Server 2005.

    I will be having a play with over the next few days and I will be sharing that in a future post.

  • Managing SQL Server 2008

    There are loads of add-ins and management stuff from Microsoft and other vendors to make taking care of SQL Server easier, and in case you haven’t noticed SQL Server 2008 has now been released so will those tools work with the new version?

    For the most part the answer is yes as SQL Server 2008 is more of the same stuff that was in SQL Server 2005, but there are new features as well.  So the various tools will have to change to reflect the new features so you can get the benefit of upgrading.

    In the Microsoft world some of these add-ins and feature packs are already available to make our management tools SQL Server 2008 aware:

    Similarly  the main tools vendors Idera, Quest and Red-Gate all have extensive support for SQL Server 2008 already. 

  • SQL Server 2008 Unleashed

    Although the official UK launch of SQL Server 2008 merged in with the launches of Windows Server 2008, and Visual Studio 2008 back in March, we wanted to celebrate the actual release as well.  So please join us in TVP on 10th September for SQL Server Unleashed.

    Map image

    The day is aimed at the DBA and will be much more technical than launch was. Kicking off the event will be Prem Mehra from the SQL Customer Advisory Team. He’s going to give you some real stories on the large scale SQL Server deployments his team has worked on.  We have also asked the MVP’s to join in with short demos of their favourite features and to take your questions at the end of the day.

    the Agenda for the day is:

    • 08.45     Registration
    • 09:30     Introduction
    • 09:45     Keynote: Prem Mehra – Working with Large Scale SQL Server Systems
    • 10:45     Break
    • 11:00     Data Warehouse Improvements in SQL Server 2008 (Keith Burns)
    • 12:00     Upgrading to SQL Server 2008 (Graeme Scott)
    • 12:30     Lunch
    • 13:15     SQL Server 2008 - Under New Management! (me)
    • 14:15     Break
    • 14:30     SQL Server 2008 BI: Making the most of your data (Scott Adams).
    • 15: 30    Q&A (1/2 hour slot)
    • 16:00     Summary and Close
    • 16:15     Networking and Refreshments
    Technorati Tags: ,,
  • Hopping Mad with Kerberos

    Like Heracles, many an IT Pro has had to wrestle with Kerberos, and for those that don’t have rippling biceps I thought it would be good to give you some tips on doing battle with this many headed beast that is used to protect many a server.

    In IT Land Kerberos is a necessary evil to enable users credentials to be passed from server to server without them needing to sign on to each box in turn.  As a Business Intelligence sort of chap I need this so that users can be authenticated on a portal and then be directed from a scorecard to a report which can come from several sources all of which reside on different servers.

    I have literally been up all night trying to get Kerberos working and failed because of some certificate issues which I still don’t completely understand.  Kerberos is part of AD and the product guys there all understand and so does any MCSE type IT Pro.  However the people that are affected by it are web admin guys, exchange gurus, SharePoint experts,  DBA’s and so on, and none of the products these guys look after (IIS, SQL Server Exchange etc.) have any tools to manage Kerberos never mind third party applications.

    Hercules doesn’t work for Microsoft, but Brian Murphy-Booth does and he has done two things to help you vanquish the beast:

    • He has developed DelegConfig, which runs from a client trying to get to a server and checks for all the common pitfalls. Brian tells me V2 is coming along wiht support for IIS7 and the latest features in Sharepoint.
    • He blogs here.

    Hopefully this will appear in a product one day or be on Codeplex, but either way thankyou Brian!

  • SQL Server 2008 not just for DBAs

    I am sure SQL Server is administered by many ‘accidental’ DBAs i.e. IT Professionals who get saddled with looking after databases, while performing a bunch of other infrastructure management tasks.  One of my good friends Jonathan Noble at Newcastle University is one of these and so I thought it would be good to give him a bit of head start to migrate and consolidate the many SQL Server 2000 databases he has ‘inherited’.

    The first thing I wanted to do was to install an environment for them to play with.  Even without any databases in, you can use the new client tools to look at and manage SQL Server 2000 and 2005 databases, and use such features as the configuration server and policy management to look at the existing estate.

    That all went well and so our focus then turned to migration and the key tool in planning any migration is the Upgrade Advisor. This is essentially a reporting tool that you can run from any server or desktop to evaluate a given server or specific database for upgrade.(the latest version here).

    One of the things you will notice about the reports when you run them is that they will always scan the msdb system database and always return these warnings if you’re examining a SQL Server 2000 server (btw the image is from the 2005 upgrade advisor):


    This is because DTS packages can reside in msdb and even if you haven’t got any packages in there you will get the warnings as per this knowledge base article (KB). 

    As I discussed with Jonathan, if you do have DTS packages to migrate then my top tip would be to manually recreate them in integration services as and when they need to be changed to reflect changing business needs. Until then run then you can edit ( to change the connections to reflect any server moves) and run them ‘as is’  by using the Backward compatibility components. In the SQL Server 2008 Feature pack.

    You can also check Jonathan’s team blog WITter

  • There’s lies, damn lies and then there’s the environment

    Every one from politicians to the IT industry is trying to raise their profile by establishing their green credentials. This causes lots confusion as they I’m greener than you arguments fly across companies, parties and even countries.  I think a few solid BI principles would be good here:

    What you measure is what you get. The UK government’s figures on carbon emissions don’t include air and sea travel and the fact that many of our goods are imported form countries with less clean energy plants. So we can all feel good that we are hitting carbon targets as we all fly and use stuff from other countries. However that’s like trying to diet while ignoring your alcohol intake!

    Similarly there’s loads of spin around e.g. “use our stuff and save the planet”, and Microsoft are no exception here. For example Vista power saving is great for the environment BUT not if you have to buy a new laptop to run it as the impact of making that laptop (I have seen estimates 11 barrels of oil per laptop) are significantly greater than the energy savings you would make over the life of  running that laptop. Then there is the disposal cost, and wouldn’t it be fun if there was a cost built into new hardware to reflect that cost like there are for new cars.  So again it’s important to be clear on what you are measuring as what you measure is what you get to quote my friend James.

    Impact.  There’s also loads of buzz about reducing server room running costs but this only accounts for 2% of  energy usage, and then something like windows server 2008 will take 15% off of that figure, which isn’t a lot. Virtualisation might cut that in half , but that’s still only 1%.

    Of course any saving is good but if you want to buy software to save the planet then that’s not going to be your top choice. My candidates for this would actually be collaborative software like Forefront, SharePoint and Office Communications Server to allow your workforce to work from home or at other sites to reduce travel, and the size of offices you have.

    Location Intelligence. Where things happen is important and actually high flying aircraft bizarrely help rebuild the ozone layer because they large quantities of nitrous dioxide they chuck out is part of the ozone formation process. Ozone at low altitudes is very bad for humans and plants as it’s toxic.  Another example is that an acre of rain forest fixes much more carbon than an acre of forest in the UK.  Putting power consumption next to power generation is a good example of doing the right thing such as Microsoft is putting server farms near to hydro electric plants.

    History. Business Intelligence data usually has rich data embedded in it, so trends can be analysed.  Historical climate is very open to interpretation, because it has to be derived form circumstantial sources such as tree ring growth, pollen , ice and soil cores and so on.  The variations being analysed are very small and the margin for error is quite variable as techniques get refined.

    These issues make analysis of the environment very difficult and my point is that if you don’t address this sort of stuff in your BI project, you’ll end up having the same kinds of debate as there are for the environment :

    • Do we trust the figures?
    • Are we doing as well/badly as we think we are?
    • My figures are different form yours?
  • SQL Server 2008 TechNet Webcasts

    If you can’t find anything to watch on the telly tonight…

    TechNet Webcast: Security Enhancements in SQL Server 2008 (Level 300)

    Microsoft SQL Server 2005 introduced many security enhancements, from data encryption to advanced context impersonation. Microsoft SQL Server 2008 further helps administrators protect their database with the introduction of new features such as Transparent Database Encryption, Extensible Key Management, and Auditing. In this session, we describe the new security enhancements in SQL Server 2008 and discuss how to best take advantage of them.

    TechNet Webcast: SQL Server 2008 (Part 1 of 3): High-Performance Data Warehousing (Level 300)

    Attend this session to learn how to incorporate the new features in Microsoft SQL Server 2008 to provide a high-performance data warehouse that can scale to larger number of users and growing volumes of data. In this session, we cover new technologies like partitioned table parallelism, star join query enhancements, and Grouping Sets.

    TechNet Webcast: SQL Server 2008 Performance and Scalability Technologies (Level 300)

    Microsoft SQL Server 2008 supports very large databases, enterprise-scale reporting systems, and data warehouses. It provides growing database systems with tools and features to optimize performance, scale up individual servers, and scale out very large databases. SQL Server 2008 is designed to take advantage of the latest hardware technologies to support server consolidation. Throughout this presentation, we look into SQL Server 2008 performance and scalability technologies and show how you can use these technologies to provide optimal performance and responsiveness for your large data solutions.

  • Must have tool for Migrating Access to SQL Server

    I have a love hate relationship with Access for the same reason, it’s seductive simplicity.  You can knock up an application in no time, which my wife did recently to learn the tool and to keep track of our extensive book collection. 

    That quick start and get going appeal, starts to pail as the application gets bigger especially when it need to be used buy more than a dozen people. Access apps are often written by business users and so the app will probably be very useful and become very popular.  At this point its existence an application like this in a large organisation may well come to the attention of the IT support team because it breaks or performs poorly.  You could argue that whoever gave Access to the users should be taken out and shot, but if the business have gone to the trouble of writing this then it is clearly useful and may well needs to be supported.

    Migrating the application from Access to SQL Server would solve many of the performance and concurrency issues, but that could involve quite a lot of work.  I mentioned that there are two migration assistants for Access in may last post, but I forgot to mention that in common with many other Microsoft tools there is often a partner developed alternative that offers better features albeit at a price. In this case the Must have tool is called MUST which I saw at the last SQL Bits. As this grid shows it provides a range of tools to help with the process

    and you can just migrate the database or go all the way to delivering a VB or web client.  Definitely worth looking at if you have a few users in your business who are closet Access developers.

    Technorati Tags: ,,,
  • Who stole my Report Builder

    Anyone who has tried Report Builder v2, aka Report designer Preview, blue etc. will have noticed that it isn’t fit for purpose yet.  By this I mean it is not in a state in which you could give it to a what Microsoft calls an information worker i.e. business user and get them to to generate their own reports. Also it currently doesn’t work with shared models or SharePoint integrated mode.  You will have also noticed that in RC0 it was included in the feature pack and that is not there anymore at all so if you haven’t got it already you are going to have to wait.

    Basically it is still in development and there will be versions coming out during the autumn starting off with an English only download around the end of August and later a click once application like Report Builder v1 that’s in SQL Server 2005 and of course multi language support. 

    You can of course continue to use Report Builder v1 but that won’t surface any of the reporting features in SQL Server 2008, such as the tablix data region and the new charts and gauges that were licensed form Dundas.

  • Dimension Jumping in Analysis Services

    Every now and then I see an internal mail with a query and I think “What were they thinking?” In this case a customer was trying to build a hierarchy that is built from attributes that are in different dimensions.

    My immediate reaction to this arises because if attributes (Say A,B & C) that make this hierarchy are splattered across other dimensions then the underlying modeling was not done properly as they should all be in the same set of dimension tables, or ideally in the same table already as I am not a huge fan of snowflake schemas.

    But there’s nothing to stop you building your cube over an operational system and so on reflection this is a valid question, so what’s the answer?

    When you are designing your dimension in the BI development studio, you need to include all of the tables you need in the design pane and to have already declared how to join those tables together in the data source view.  You can than drag in the attributes you need into the dimension where you need this hierarchy.

    If you need those attributes in another dimension to take part in another hierarchy then you just reuse the tables you need in the designer for that dimension.

    So as usual the customer is always right!

  • More secure is not totally secure

    My good friend Steve the team spook, has written this post about SQL Servers track record on security compared with what many people perceive is the secure database of choice, Oracle. I can only speculate as to why this is the case as I am not an Oracle guru, but it may well be down to Microsoft’s trustworthy computing (TwC) initiative whereby products have a 3D process for ensuring security is a prioirty:

    Secure by Design. Stating the obvious here but the products need to be designed to be secure from the ground up. The Microsoft Security Development Lifecycle (SDL) is widely acclaimed and the rest of the software industry has nothing like this particularly in the open source world. But it’s important that this sort of approach is widely adopted so the links above enables others to adopt it, as an organisation is only as secure as its least secure system.  Vulnerabilities dropped from 16 to 3 when it was applied to SQL Server 2000(read more here).

    Secure by Default. SQL Server , Windows Server 2008 etc. installs with what James O’Neill on our team and James T Kirk on the Enterprise would call “shields up”.  That is to say no ports are open and all features are off so you have to specifically open up just the bits you need, like being able to access SQL Server from a remote machine, and setting up TCP/IP and named ports connectivity. So you setup the environment the way you want and then open up the connectivity when your happy with it, like the way you don’t let people into a new building until it’s safe and signed off by the dreaded health and safety.

    Secure by Deployment.  Microsoft is not perfect (no really it isn’t), and new threats are coming out all the time so fixes patches and advice are released as soon as possible.  For example, the most recent set of security updates for SQL 7, 2000 and 2005 resolve four privately disclosed vulnerabilities. For details of the affected systems and vulnerabilities see Microsoft Security Bulletin MS08-040. If you have already applied Cumulative Update 7 back in April or higher for SQL Server 2005 SP2 then you have these fixes as they were included.

    There are also shed loads of Security white papers and advice for example:

    Finally if SQL Server really is insecure, then show me the evidence as I am sure if there were any serious issues they  would be all over the Register, FARK and Slashdot like a rash in about 10 nanoseconds and then sit their for centuries.

    Technorati Tags: ,,