Insufficient data from Andrew Fryer

Data Centre Resource Planning and System Center 2012

Andrew.Fryer — Fri, 24 Feb 2012 18:51:08 GMT

Most of us like have the proper tools for the job; trying to undo Phillips screws with a penknife, using your gold card to get the ice off the windscreen or using notepad to write F# is no fun. I would argue that the proper tool for running a modern Data Centre is System Center not just to save money as this report from last year’s War on Cost event by Inframon suggests..

but so that IT keeps pace with what the business demands of it.

That’s easy to say but how do you justify the expense of implementing it, I don’t mean the license costs rather the time and effort taken to adapt its potential to the needs of your business. I would liken this to investing in an ERP (Enterprise Resource Planning):

You could mash together a series of tools and utilities that you have in place already or that cam bundled with some of your infrastructure. However the ability of each part of an ERP system to understand the resources, people and assets and have one mechanism for creating business process automation does offer economies of scale and the ability to adapt process far more quickly than would be the case otherwise
ERP systems are about managing all of your resources, while System Center this is just limited to your IT infrastructure, however the similarities are striking
- You define process and and standards with the business. Service Manager and the
- the approval processes for getting resources automatically spawns the task to create those services
- The use of resources needs to be accounted for
- You use reporting and analytics to monitor activity and predict demand
As any ERP consultant will tell you, there’s a great career to be had in implementing these systems and I am sure that individuals who embrace this vision as well as companies like Inframon will find plenty of projects to work on. Not only that there is a whole ecosystem around this implementation from, audit & compliance to security and business intelligence.

I mentioned the War on Cost event earlier and General Nathan Bedford Forrest was once misquoted as saying winning a war is about getting there “fustest with the mostest”. Applying that to your career in these increasingly cloudy times I would advise you to get there fustest by learning about System Center 2012 on the Microsoft Virtual Academy or at our rapidly filling IT Camps before your peers and getting their mostest by looking out for the upcoming System Center 2012 certifications

Managing a hybrid cloud with System Center 2012

Andrew.Fryer — Fri, 17 Feb 2012 10:23:00 GMT

Hybrid cloud is the term applied to the increasingly common scenario where a business runs some of its services in the public cloud but retains some on premise, although it is also possible to use assets from both to provide a service as well. For some business this is just a transitional step as more and more services are moved tot he public cloud but for many organisations this might be the long term situation as some services can only be run internally.

For services /applications running in your own data centre you have a large degree of control over everything that application runs on, and you can tune and tweak that as you see fit. You get slightly less control as you outsource use hosting or run on someone else’s Infrastructure as a Service cloud platform. However with Platform as a Service like Microsoft Azure there is less to manage; you loose control of the operating system and only have limited ability to set storage, and compute resources. This is a good thing as it increase agility and reduces management overheads, but it does still need management.l

This loss of control reminds of the early days of banking, you lost control of where your money actually was in fact you couldn’t point to a fiver in a bank vault and say that it was yours. However you still had control of which types of accounts to out your money in and you would be foolish not to request and make decisions on the bank statements you were sent. In the electronic world of the cloud you don’t have bank statements you have something like the Azure Management Portal..

which shows you how your services are deployed and allows you to change them as you see fit for example to deploy more web front ends, change the schema of your SQL Azure database..

and so on..

That’s fine if you run all of your services in the public cloud, however if you also have a data centre you’ll probably want to see an overview of how everything is working and be able to report to management on how all your services are performing no matter where they are. Not only that you’ll want one set of tools to make whatever changes you need bearing in mind the limitations of what can be changed in a cloud service. There are several tools in System Center 2012 to directly interact with Azure and whatever you have running in your data centre to provide the monitoring and control you need;

System Center Operations Manager has an Azure management pack to report on your Azure performance and setup in addition to a raft of other management packs to manage databases, switches, hardware, various operating systems, and numerous applications. The Azure management pack has to e configured with certificates and then uses Azure storage as an intermediate cache for storing and then transmitting the required telemetry down to the local Operations Manger database.

Note this can all be done in Operations Manager 2007 R2 (CU3 or later)

System Center Application Controller is a new tool for System Center 2012 that provides a web portal showing what is running in your private and public clouds. The private cloud works by pulling in data from System Center Virtual Machine Manager 2012. This latest release of Virtual Machine Manager now allows private clouds to be created and managed on different hypervisor platforms; Citrix Microsoft Hyper-V and Vmware. Application Controller also connects to your Azure services using a management certificate (as Operations Manager does) to secure this ..

Delegated users can create or enhance their own services from here.

However for more control you may wish to create more rigorous but still automated process using your help desk (e.g. System Center Service Manager, Remedy etc.) hooked up to these tools. For that there is System Center Orchestrator which allows run books to be setup with approval process, to capture additional data such as cost centre department etc. and carry out additional configuration tasks.

One thing to note that is this is all IT Professional stuff, System Center 2012 is designed for us, not for bank managers, marketeers or accountants. With my business intelligence background I would also recommend that for large and complex organisations you overlay System Center with a BI solution like SharePoint, coupled with web parts for them to be able to initiate service requests. A lot of the tooling for this is already in System Center 2012 e.g. there are optional analysis services cubes in Virtual machine Manager, and extensive reporting services reports in Operations Manager.

So plenty to think about and keep us busy in 2012, and if you want to see some of this in action before trying it out yourselves (System 2012 betas can be downloaded here), you can come to one of our IT Camps on System Center.

Microsoft System Center and BMC Management Tools Integration and Interaction

Andrew.Fryer — Tue, 14 Feb 2012 09:02:00 GMT

In his second guest post Greg Charman from Kelverion has a look at BMC integration.

Many customers have made large investments in both Microsoft System Center and BMC Datacenter Management Tools. Historically integrating these two vendors product sets has been challenging and time consuming.With the purchase of Opalis (an IT Process Automation Solution) in December 2009 and the release of Opalis 6.3, but particularly with its replacement System Center 2012 Orchestrator, Microsoft has laid the foundations for a much easier and speedier interaction between the two product suites.

The challenge with any integration and automation product is keeping the interfaces to vendor products current and when a vendor has many management products, like BMC does, this only becomes more complex. In the newer versions of their Enterprise Management products BMC have updated the product APIs (Application Programming Interfaces) to web services APIs. This now means you can now build your own interaction to a BMC application in Orchestrator if it has a web service API using the Standard Activities within Orchestrator particularly the Invoke Web Services Object. To create a DIY interaction with a web service API in Orchestrator you need to have completed the following tasks in your runbooks:

1. Understand in the Published API so that you know which methods to use interact with the API and know how the results will be returned to you from the API.

2. Create a Pre-processing Runbook to generate the Security Key which has to be passed to the Insert Into or Extract From API Interaction Object

3. Create the Insert Into API Interaction Object which formats data from the published data bus into the right XML format for the API method

4. Create the Extract From API Interaction Object which submits the correct XML format for the API method so that the data you want is extracted.

5. Create a Post Processing Runbook to manipulate the output of the API extract call into a format which is easily useable within Orchestrator

This DIY solution often takes 3+ days to create, per interaction you want to achieve with the target system. This is fine, in principal, if you only want to do one or two simple interactions with the target system but if you are looking at say a Service Desk application as a target you won’t be looking at just one or two interactions, you will want many interactions; create incident, update incident, close incident, create change, update change, close change etc.

However if building it yourself all sounds too complicated then there is an alternative, Kelverion announced recently the planned availability of 4 new Integration Packs (IPs) for both Microsoft System Center 2012 Orchestrator and Opalis 6.3. Kelverion already have a close partner relationship with Microsoft, including the development of third party Integration Packs, and have partnered with BMC to bring the new IPs to market for the benefit of joint Microsoft and BMC customers.

The new IPs announced target integration with:

• BMC Atrium CMDB 7.6 - Federates data from across IT into a single, logical data store.

• BMC Bladelogic Server Automation 8.1 - Management and enforcement of server configuration changes

• BMC Remedy Action Request System 7.5 and 7.6 - Platform for managing Service Management business processes

• BMC Event Manager 7.4 - Proactively correlates and prioritizes events within a real-time business context

Beta Release Schedule Update

• BMC Atrium for Opalis 6.3 Released

• BMC Bladelogic - Released (Opalis and Orchestrator versions)

• BMC Remedy February 2012 (Opalis and Orchestrator versions)

• BMC Event Manager February 2012 (Opalis and Orchestrator versions)

• BMC Atrium for Orchestrator March 2012.

Summary

If these integration packs are of interest to you and you want to know more updates will be posted at http://www.kelverion.com/new-integrations/ or you can contact David Wooster for more information.

Editors note Kelverion have also written quite a few of the new Orchestrator Integration Packs for Microsoft, and you’ll see these being released as we gat closer to the final release of of System Center 2012 itself

Exploiting Orchestrator - Tips to avoid the hidden bear traps

Andrew.Fryer — Mon, 13 Feb 2012 08:36:04 GMT

Following on from my last post about getting started with System Center 2012 Orchestrator, I asked a good friend of mine Greg Charman from Kelverion, to share some of his experience.

System Center 2012 Orchestrator and its predecessor Opalis 6.3 are a very new subject to many IT Professionals and using IT Process Automation tools is not just as easy as install them and then learn on the job. Each ITPA tool is fully customizable to support the business process you wish to implement so they largely start as a blank sheet of paper.

It is very easy to install Orchestrator or Opalis and create simple workflows but very quickly people find themselves hitting problems when they want to do more complex activities like branching, looping, persisting data and monitoring process completion. There are pitfalls to be avoided when implementing the solution and although some information exists, the reasons why you should avoid these and the correct approaches are not so well documented.

After more than 4 years of using and implementing Opalis and Orchestrator I have learn what to do and not do to make the implementation successful. Some of the key points for success are as follows.

Things to do before you start:

1. Configure the Designer Client – in Orchestrator and Opalis you can define the behaviour of the Runbook/Policy Designer Client. There are a number of settings you can adjust but the key ones you want to set are;

Turn on Show Link Labels – Then you will be able to see the Labels that have been added to the Links in the runbooks
Turn on Automatic check out – The benefit with Auto Checkout is that it is easy in Orchestrator to make modifications to a Runbook and then find you haven’t checked it out which means all your changes are lost. Orchestrator does not prompt you to Checkout if you try to make a change.

Things to be avoided:

1. Counters - Never use Counters in Orchestrator as they are Global Counters and can thus be modified by any Runbook at any time. You cannot therefore rely on their value at anyone point unless you are running in a Single Thread. This defeats the fundamental benefit of Orchestrator - parallel execution and multi-threading.

2. Variables - Variables in Orchestrator are also Global. When you export a Runbook all the variables in Orchestrator are exported not just the ones in use in the Runbook, it is therefore very easy to pollute your installation with variables gained from imported Runbooks. Therefore use them sparingly; they are ideal for Database Server Names, Table Names etc.; which will be used in many runbooks.

3. Text Files for data storage or processing – using text files for persisting data or data manipulation in a Runbook is a recipe for disaster. Text files can only be accessed by one process at a time, the file is locked whilst interaction takes place, you therefore force Orchestrator to become Single Thread. This defeats the fundamental benefit of Orchestrator - parallel execution and multi-threading. You will also find you will do a huge amount of post processing manipulation when you try and extract the data back out of the text file and the post processing is not a simple or pretty activity to implement in Orchestrator.

Best Practices:

1. Implement Orchestrator with a Runtime Database – if you want to persist data, have a run time variable in a Runbook, dynamic input data, complex look up logic, audit History, you are struggling to parse data or have got XML/CSV as an input or have got to manipulate the data, then put it in a Microsoft SQL Server Database and use the Database Activities to process it.

Why put it in a Microsoft SQL Server Database? Because all the database objects are much more powerful against Microsoft SQL than any other database type.
You design this database to meet the needs of the IT Processes you are automating in Orchestrator and the needs of the Runbooks you are creating.

2. Keep Monitor Runbooks very short - Got a Runbook which starts with Monitor some enterprise tool and when new/update event do something?

Keep the workflow very short for maximum performance. Grab the event and write it to a Microsoft SQL Server Database and use a Database Activity to process it. This way there is less chance of Orchestrator missing a new event because it is still busy processing the last set of events.

3. If you are not already a database guru then Kelverion also offers a Database Integration Pack for Orchestrator and Opalis, which gives you easy script free interaction with databases from within Orchestrator or Opalis and formats the columns selected from a Database Table into individual items of published data – no more need for the dreaded [Field(…..)] statement.

Summary

It is difficult in a blog to emphasise how powerful Orchestrator becomes when implemented with its own run time database. There has been nothing I haven’t been able to achieve using Orchestrator and a runtime database but there has been many instances where things could not be achieved if a database was not in use.

There is a good Microsoft MVP book by SAMS press called System Center Opalis Integration Server 6.3 Unleashed which provides information and guidance on Opalis implementation. Although written for Opalis all the principals apply to Orchestrator.

If you don’t fancy learning everything from a book or you would like more hints and tips then Kelverion provide a range of training courses and offer bespoke training offering based around specific customer requirements. Kelverion is an established systems integration and software development organisation specializing in IT Process Automation solutions, founded in April 2010 by previous employees of Opalis.

For more information on Kelverion Training and our other offerings got to www.kelverion.com/itpa

Superglue now available for System Center 2012

Andrew.Fryer — Thu, 09 Feb 2012 19:18:39 GMT

One of my frustrations to date when showing off System Center 2012 is that it doesn’t quite fit together yet; each of the individual products is in beta and works pretty well, however joining them up is not there yet. This is not because there’s a flaw in the line up or missing functionality, it’s just that the bit that really makes System Center an integrated suite, namely System Center Orchestrator hasn’t had the integration packs available for it to work with the other new versions of the suite. Until now that is..

The new 2012 integration packs are available here, and in time will be incorporated into the System Center 2012 unified installer when the suite is released to manufacture (rtm).

The integration packs then need to be applied to Orchestrator (this can be downloaded individually or as part of the whole suite here). This is process is broadly similar to how you setup management packs in Operations Manager if you are familiar with that, but I thought it would be useful if I walked you through it, and I have put a slide deck together of the steps to get you started including setting up Virtual Machine manger 2012 inside the Runbook designer once the IP is configured..

One thing I find a bit counter intuitive when designing run books is the way you pick up variables in each step, so I wanted to put that in the deck as well. The trick is to remember to right click on any filter or text in any of the details properties in an Orchestrator activity to pick up variables that are flowing along as each activity in a Runbook is processed. For example in this screenshot I want to stop a VM and to do that I need the VM ID. Upstream from this step I have entered a VM name and then used an intermediate step to get the properties of that VM including the ID.

and from there you can select any variable on the Orchestrator data bus to match against or use

If I then test the Runbook it will run for real and I can see that it’s calling Virtual machine Manager to stop my VM by opening the Virtual Machine Manager console and looking at the most recent job..

I can go on from here and maybe reach out to Operations Manager and put the server into maintenance mode or hook the Runbook up to Service Manager and hook it up to a service request but you get the idea.

To conclude this is one of the key ingredients in Microsoft’s Private Cloud and now the IPs are there to make calls to the other parts of the System Center suite. I’ll be showing this off on our IT Camps as we tour the UK this spring/summer and there will be more in depth content on line at the Microsoft Virtual Academy

Application Platform Knowledge Hub

Andrew.Fryer — Tue, 07 Feb 2012 07:51:59 GMT

Albert Einstein once remarked that you don’t need to remember everything, you just needed to remember where to look. When it comes to learning about Microsoft products this can sometimes be harder than it ought to be and what resources there are can be fragmented so you do have to remember where to look. That’s why I have been trying to get you to remember look at the Microsoft Virtual Academy as that is a great learning resource for all things cloud including, Azure, Hyper-V and more recently System Center.

However that resource is never going to have anything on the other technical love of my life, SQL Server, and getting up to speed on that is hard if only because it has been around for longer and you will want to learn about the version you have and possibly the version you would like to have. Until now that is because the some of my colleagues in the UK have decided to do something about that and created the Application Platform Knowledge Hub. A quick glance at the opening screen gives you the idea:

As you can see each topic (in my cases High Availability) has content for SQL Server 2005 through to SQL Server 2012, and is skill ranked as well so you can get as deep into a topic as you need to an also find that whitepaper to waggle under your managers nose that he’ll understand.

Like the Microsoft Virtual Academy there is also a section on Azure and because this is hub is about development there also some good resources on there about Application Lifecycle Management (ALM).

So now you’ll only have remember where this portal is and all I’ll have to do is to remember I wrote this post!

What’s good in SQL Server 2012 Integration Services

Andrew.Fryer — Mon, 06 Feb 2012 15:24:25 GMT

This is a guest post by Allan Mitchell MVP, and one of the top speakers at SQL Bits

The launch of SQL Server 2012 is not too far away and the SQL Server team have been hard at work adding new functionality and also improving upon existing features. I am lucky enough to have been working with SQL Server since version 6.5 and have seen it gradually become the great product that it is today and will be in the future.
My passion has always been around data quality and the movement of data. Microsoft in SQL Server 7 introduced a new tool called Data Transformation Services (DTS) for which I still have a special place in my heart. In SQL Server 2005 they completely rewrote this tool and gave it a new name "SQL Server Integration Services (SSIS). SSIS was a paradigm shift from DTS and took some getting used to. SSIS in SQL Server 2012 is certainly a release that improves on the existing release and this article will put forwards my two favourite things in SQL Server 2012 SSIS. Your choices may vary but this is my article and I get to choose my two things ;)

The SSIS Server and Catalog.

At first I did not like this at all. When I spoke to people like Matt Masson on the development team I was never very positive about it. Looking back though I think I was being stubborn and not wanting to accept change. I am quite happy to admit I was wrong. It is my opinion that if you want to use SSIS in SQL Server 2012 properly and get the most from it then you are going to want to look at the new Project Deployment mode and the SSIS Server.

Using the SSIS Server and catalog gives you a huge amount of functionality including but not limited to;

Data Taps: The ability at runtime to "tap" into a part of the data flow and extract the data flowing through. Incredibly useful for debugging.

Environments: Think configurations but better. You create an environment in which your package will run. You specify values for parameters and properties in your package and map in them from the environment and off you go.

Reporting: Reporting in a version of SSIS prior to SQL Server 2012 was pretty basic, frustrating and even bordering on pointless. Not any more. The team have obviously thought about what we want (and they have been told through Connect). Now you can get proper SSRS reports showing you phases in your package along with durations and trending of performance over executions.

WARNING. If you are an SSRS pro then the reports are functionally very good, aesthetically not so much. You may also want to look at Jamie Thomson's reporting pack to compliment what comes out of the box

The User Interface.

For a long time now I have been telling anybody who will listen that the weakest part of SSIS is the Business Intelligence Development Studio environment. Once SSIS is into runtime it is pretty stable and does exactly what it was asked (not necessarily what you wanted, but what you asked).
The SSIS toolbox for example is currently mashed in with the Visual Studio toolbox meaning a whole load of things have to be loaded that have no relevance to the package you are creating
Precedence constraints just disappear from the UI. They are there underneath the covers you just can't see them. To this day I have no idea why this happens occasionally but what I do know is that it is almost impossible to develop a package when this happens and you have to restart the package for the designer to redraw the lines.
The UI for developing SSIS packages in SQL Server 2012 has been rewritten in the Windows Presentation Framework. The edges on the tasks and data flow components have been rounded off resulting in a 10% improvement in speed due to less resistance (only kidding about the speed improvement). The UI has the look and feel of a proper Visual Studio UI. The SSIS toolbox lives by itself. No more loading of not-needed components. I am very happy with the new UI, very happy indeed.

Summary

This article has covered two of my favourite things coming in SSIS for SQL Server 2012. By no means are these the only changes. Go take a look at parameters for instance. The SSIS team have been hard at work for this release. There are some things I don't like but there probably always will. That's why we have Microsoft Connect so we can tell the team about these "opportunities" for improvement.

Great SSIS Resources
The official SSIS team blog
Jamie Thompson MVP with his SSIS Junkie’s blog

Go on, give it try and download SQL Server 2012 RC0

Event: IT Camp Leeds Feb 28th

Andrew.Fryer — Wed, 01 Feb 2012 08:07:00 GMT

Back in December Simon and I invited a few friends along to help us beta test a different kind of event, an IT Camp. The idea is simply to do smaller, more interactive events where discussions and questions are encouraged. The presenters, (Simon and I) being a bit more in the background. The first of these events went down very well, but in this spirit of interaction we were keen to understand what we could do to make the camp even better. Having made some changes based on the feedback from that test event we are now ready to go on the road and our events team are looking out for regional venues so we can come and see you rather than you travelling down to London or Reading.

There are actually four types of camps designed to complement the online resources we have on the Microsoft Virtual Academy site:

Camp 1. Server Virtualisation. This is for those unfamiliar with the latest version of Hyper-V in Windows Server 2008 R2. We will explore all the basics including networking and clustering and run through a short introduction to using System Center Virtual Machine Manager 2012

Camp 2. An Introduction to Microsoft’s Private Cloud. This builds on the first camp and explores all the key features in System Center 2012 that enable cloud like processes and capabilities in your own data centre.

Camp 3. Server Migration. Specifically the tricks and tools from migrating your various server workloads from Windows Server 2003 to 2008 R2,

Camp 4. Consumerisation of IT. This explores the issues and benefits of integrating your users’ own devices with your infrastructure, such as slates, smart phones and laptops.

You’ll see more coming out about this in the TechNet newsletter and the various dates and venues are on the IT Camps page on the UK TechDays site, shortly, but I wanted to specifically mention that the first of these camps will take place in Leeds on 28th Feb. This will be a Camp 1 server virtualisation event. Our plan on the day is to work together to build a Hyper-V cluster using your machines as well as our own kit. You don’t have to use your own machine if you don’t want to but if you have a laptop with 20Gb of free space on and 4Gb of RAM and it can run Hyper-V then we’ll take you thought the steps to set up Hyper-V without affecting what you have on there already.

If you follow the links you’ll see that this event is by invitation and this is because we want to try and ensure the right people attend as it is limited to 70 delegates. So who are the right people? For this event it will be Techie IT professional types, who are only now becoming interested in what Microsoft is doing with server virtualisation, perhaps because they are using something else or their business for whatever reason is only now considering a move to use this technology, i.e. it’s not really for Hyper-V experts. If this sounds like you or a colleague then drop me an e-mail and I’ll send you the code, and you’re in.

Anywhere Working actually works

Andrew.Fryer — Thu, 26 Jan 2012 13:16:00 GMT

It’s Work Anywhere week but working anywhere isn’t working everywhere. By that I mean that not all organisations allow their staff to work away from the office even where this is possible and even sensible. So here’s my bluffers guide as I do this quite a lot. At first sight working away form the office might seem all good for the employee with no obvious benefit to the employer, however the reality is that there are downsides for employees and loads of benefits for employers.

Lets look at the employer first and the downsides of Anywhere working. I guess the obvious concern is loss of control, not knowing if your staff are doing what they ought to, and vague concerns about productivity.

However there is a big price to be paid for having all your staff on site, not least the cost of that site. If a business implements anywhere working then it might only need desks for 60% of the workforce and not 80-90%. My counter to the productivity argument is threefold:

1. Your workforce are dependant on personal and public transport to get to work and failures in these also lead to a loss in productivity. If I cast my mind back to the snow and ash clouds last year, our anywhere working and unified communications meant that very few customer meetings and events were cancelled.

2. Work Anywhere doesn’t just mean working at home. If your workforce can get unified communications and some sort of VPN access to get at internal resources form a remote location. They can work on a client site, at public events like trade shows, and in coffee shops, hotels etc. In my own case I worked at my mum’s house while she got over a cancer op at the start of the year, so I could care for here and get stuff done, and then when I was at a big show at Olympia the following week I could access internal SharePoint sites to get answers I needed to the many questions I was being asked .

3. The other major loss of productivity is sickness and if you can work at home in a reduced capacity they won’t bring their germs to work and affect the rest of their team. This happens all the time at Microsoft and its not just colds it’s post trip jetlag, sports injuries and in my case working at home after an emergency appendectomy. Of course this requires two trust between managers and staff but that should be there anyway. Migraines are my problem here and I can just work round them with the trust in place between me and my manager.

The upsides for us employees are all pretty obvious, in my case looking after mum after her cancer surgery, burning the midnight oil in the week to make a swift exit on Friday lunchtime, and working in New Zealand for a day while on holiday as you can’t really have 5 weeks off back to back without doing a check-in and some e-mail triage. Another great benefit is having stuff delivered at home rather than endless trips to the post office track down your latest Amazon & EBay purchases. However there are some downsides:

It’s all too easy to check e-mail whenever so you need to set aside time to unplug from the office
If you are unable to work you need to say so and take time out, no one is indispensable!
You’ll save money and a lot of time by not commuting to work but season tickets on public transport aren’t economical for a once or twice a week trip to the office.
Another cost is that your energy bills at home will rise as you’ll need to heat & power home office.
Most employers will cover your broadband costs, but the cost of having your own home office will be down to you

So I think Anywhere working is a good thing and while I am more than happy to work for Microsoft even if they didn’t support this, I am more effective and productive because they do in principle and in practice. Check out Anywhere Working for more on this and encouraging your organisation to think about it, for example I punched in my daily commute and assumed I would work at home 3 days a week..

DTS RIP

Andrew.Fryer — Thu, 26 Jan 2012 12:18:26 GMT

Data Transformation Services is the bit of SQL Server that helped pay for my house, car and some really nice holidays. I first got to use it some 12 years ago and after working with BCP (bulk copy program) it was just so easy, I was sucking data out of AS400/RPG, Oracle and Sybase to create data warehouses in weeks rather than months. However to be fair it did have its faults, but for a free utility bundled with SQL Server (7 & later) there was no comparison.

However those faults became more important as it was used more widely and it lacked many of the capabilities of the standalone tools that were also around at the time:

It didn’t really have many good transformation tools so you either wrote your own in ActiveX or you did the work in views/ procedures on your target database. This generally resulted in lots of temporary tables in your data warehouse and lots of disk activity.
Flow control was pretty basic, for example if you wanted to pick up and load all the excel files in a folder, you would have to do that in code yourself.
Parameter passing was pretty basic if memory serves.
The moving form development to test, to production was tricky because data sources were fiddly to manage. In fact I remember embarrassing myself by issuing a truncate table statement on the source instead of the target at one client site, fortunately they had backups!

DTS Packages like this can be really hard to understand (thanks to Neeraj Nagpal for the screenshot)

There was no easy way to modify DTS to add these capabilities and so SQL Server Integration Services (SSIS) came out with SQL Server 20005 late in 2005. However You could still run those old DTS packages inside SSIS and even edit them if you needed to (details are here for running DTS in SQL Server 2008 R2). This side by side capability continued in SQL Server 2008 & R2 but DTS was specified as a deprecated feature in SQL Server 2008 and this is advanced warning that running DTS packages won’t be supported in the next version. That next version SQL Server 2012 is now out in beta and as stated there is no support for DTS in it.

I first wrote a post about this problem nearly four years ago, and my advice at the time was to do a gradual migration from DTS to SSIS where when a significant change was needed to a DTS package you would reengineer it in SSIS. Another option is to use DTS xChange from Pragmatic Works which does cost money but makes a very professional job of automating the conversion into a well designed SSIS package with good design and proper logging. Finally you could just get in some data warehousing experts and they’ll do the work for you.

Whatever you decide DTS is pretty nearly dead, and while I do have a soft spot for it, once I learnt SSIS I realised how much was missing in DTS.

Some SQL Server 2012 upgrade advice for ISVs

Andrew.Fryer — Wed, 18 Jan 2012 15:35:00 GMT

I have spent a lot if time recently briefing Independent Software Vendors (ISVs) on SQL Server 2012,so I thought a consolidated post on the subject might be useful for those planning to develop solutions on top of SQL Server 2012.

New Features that will just work

By this I mean there are some new things in SQL Server which you can take advantage of without changing your application.

Always On allows you to make an application highly available by combining the best parts of mirroring and clustering without the need to have a SAN or other shared storage. Not this is in enterprise edition.

Report Alerting allows end users to setup conditions in simple interface on any report and get an email when those conditions are met. This needs SQL Server standard & SharePoint Foundation (the free one) or higher.

New Features that you can take advantage of in your application

Development SQL Server now has SQL Server data tools that you can deploy to Visual Studio 2010 to make application lifecycle management easier. For example simple tools to edit and compare schemas and data tier applications to make deployment of your application easier. There is also Distributed Replay, which allows captured profiler traces to be replayed on another environment which might be a later version of SQL server or simply a test server. the tools can either be installed as part of installing SQL Server or via the web platform installer

Security. The key security feature in SQL Server 2012 for ISVs will be contained database security which will allow you to have all the security credentials built into the database you are using.

File Table. This builds on filestream to expose a new type of table a file table as a folder that can be used as any normal file folder except that each file and subfolder will now be stored as a row in the File Table. This might be useful in storing any unstructured data as part of your application. Note that full text search and the new semantic search work well with File Tables. I have post here on setting that up too

T-SQL. There are a few new functions in T-SQL, that might be relevant.

Self Service BI

In many situations the end user will want to combine data from your application with other sources. The new self service BI capabilities in SQL Server 2012 can make it easier for users to do this in Excel and for this work to be scaled up and deployed to the rest of the business. To get the best out of this in your application you might consider:

Creating a suite of reports designed to expose the dimension type information (product lists, chart of accounts etc.) that can then be consumed by the user as odata feeds in PowerPivot for Excel (odata is built into reporting services).
Creating a BI Semantic model to map how your data is structured and add extra business logic (calculations and aggregations) so that business users can quickly build their own analytics and report in such tools as the new Power View. note: This requires SQL Server BI edition and SharePoint enterprise

What won’t work

There are a only a few things that won’t work in SQL Server 2012 that are in SQL Server 2008 R2. Microsoft has a process for announcing which features will go; in any given release there are a list of deprecated features, those that won’t be supported in a future release. This means there is plenty of advanced warning, both to stop using the feature if you are already and not to use a deprecated feature in any new design work.

In SQL Server the list of features that are no longer supported is very minor; i.e. if it works in SQL Server 2008 / SQL server 2008 R2 it will also work in SQL Server 2012:

System stored procedures:
- sp_ActiveDirectory_Obj
- sp_ActiveDirectory_SCP
- sp_ActiveDirectory_Start
the Surface area configuration (SAC) tool
and various command line switches to install SQL Server so if you are deploying SQL Server as part of an application you’ll need to change the install script. I mention this because various tools like the upgrade assistant will pick up what code is in your database and profiler and the SQL Server deprecated features object will track your usage of features that are going to be obsolete, there aren’t really any tools to check your installation process.

Upgrade Advisor and Upgrade Assistant

Two confusingly named tools exist to put some science into your upgrade planning, the SQL Server Upgrade Advisor is a Microsoft tool, and the Upgrade Assistant is also free and provided by a top gold partner Scalability Experts. The Upgrade which does a high level check of compatibility issues moving from one version to another, and the Upgrade Assistant is a detailed tool for preparing making trace replays to confirm that the code that is actually executing in an application works in the new version, so this can be used to track an installation code executing in multi tier applications as well as the objects inside any given database. If you’re an ISV you’ll probably want to use both in your testing.

DTS

DTS won't be supported in SQL Server 2012 for more on this check TechNet and my post on the subject.

Anyway I hope that’s useful, full details on SQL Server 2012 Editions & Licensing are here, and for more information on those new features visit the main SQL Server 2012 Resource Centre.

Educated desktops

Andrew.Fryer — Mon, 16 Jan 2012 10:44:57 GMT

Simon and I have spent most of last week on stand duty at BETT one of the largest education events in the world. We were there to field questions from teachers and some of the hardest working IT Professionals, those supporting the IT in schools. Agility is essential to cope with the new influx of students every year as well as is the need to deploy every more applications to keep up with the latest standards for the curriculum and the way each subject is taught. Some of these questions are relevant to all of us so I thought I would post some of the discussions..

Teaching the next generation of IT Professionals.

There was a lot of coverage in the press last week about teaching coding and development as part of ICT, however I had two separate requests from ICT teachers about teaching how to maintain and fix problems on PCs, because that’s what their students had asked for. We discussed setting up virtual machines on Hyper-V and using snapshots to allow a damaged desktop to be fixed and then being reset with the problem for the next lesson. I also think some of the information on clustering and virtual machines on the Microsoft Virtual Academy could be reused in class rooms.

Remote Desktop Services & App-V.

One way to deal with the problem of matching up students and teachers to the applications they need , irrespective of where they are working is to use App-V (application virtualisation) as this deploys a virtual copy of an application to a desktop based on the groups a user belongs to i.e. it won’t show up in programs in control panel and can run side by side alongside earlier versions of the same application which it would normally conflict with.

Another approach is to use Remote Desktop Services (RDS) and it was no surprise at BETT to see all the hardware vendors sporting their latest thin client devices, and personally I like the LG and Samsung offerings where the thin client was just part of the LCD panel. However not every application likes running as a remote desktop and you can end up creating a lot remote desktops for each type of user. The trick here is to use App-V with RDS so that the applications run virtually inside the remote desktop session and a given user only gets the applications they need even though you only have one or two standard desktops in RDS (the guidance on how to do this is here).

Another good thing about RDS is that it reduces heat in the classroom if thin client devices are used and also reduces the background noise, although the noise from pupils will still be the same! It is possible to implement RDS without also deploying Citrix or Quest technologies on top, however both of these partners’ offerings add ease of use and manageability to what the raw RDS experience delivers.

Digital Inclusion

RDS can be setup so that these personalised remote desktops are available to staff & students working at home or other locations and this means they can use their own devices to interact with a school. Of course laptops are expensive and can be difficult to justify on a limited budget, so to level the playing field there is Get On Line @ Home, which provides affordable reconditioned hardware with Windows 7 + Office 2010 with telephone technical support included.

..and Finally

One of my colleagues was asked for a whitepaper so he naturally wanted to know on what topic as we have loads of them, the answer came back “no I just want some whitepaper” and the delegate grabbed some blank A4 sheets off the stand!

ITIL and System Center 2012

Andrew.Fryer — Mon, 09 Jan 2012 14:19:15 GMT

If ITIL is the Why , the What and the When of IT operations System Center provides the how..

I have been quite harsh with the tick boxes here, for example you could argue that part of Service improvement could be to redesign a process and this could be done in System Center (Orchestrator), and validation and testing management is in yellow as this requires Visual Studio Team Foundation Suite with its Lab Manager extensions to Virtual Machine Manager. However I can’t see System Center being used for evaluation management particularly as one of the choices being evaluated could be System Center itself!

ITIL is vendor agnostic, and so it isn’t dependant on anything from Microsoft and because of this it is more focused on the management structure rather than how things get done. To turn those broad concepts into practical reality in a Microsoft world, there is the Microsoft Operations Framework (MOF). This is not a product, it’s a free solutions accelerator designed to help you get the most out of your Microsoft infrastructure using ITIL like best practice, and included in it is a cross reference between MOF 4.0 and ITILv3.

MOF is constantly updated, not just to keep up with the best practice to deploy the latest versions of SQL Server, Exchange etc. but also to stay abreast of current infrastructure thinking like Private Cloud.

No one seems to have time to change how to do IT in a very lean economy and adopt these processes, however we do all seem to have time to rework, patch and fix the the less than efficient methods we have in place today. Therefore all I am suggesting is to free up a little time planning and getting to grips with ITIL (and MOF if you are a Microsoft based outfit) to save lots of time fixing later, which I believe is more rewarding for you and for your business.

How to Plan an ITIL Infrastructure Implementation

Andrew.Fryer — Wed, 04 Jan 2012 08:12:57 GMT

In the last guest blog post from Erin Palmer he looked at how to manage IT chaos with the adoption of ITIL infrastructure. In this second guest post he takes a more detailed look at five key points from the TWDC case study that can help plan a successful ITIL implementation process of your own.

1. Generate the Buzz

ITIL adaptation takes skilful planning, a strategic implementation schedule, and the participation of key players who will be in full support of the transition. Follow the success of TWDC’s strategy and help your constituents see how the adaption of an ITIL infrastructure can address current IT concerns and will help them use IT more efficiently. Implement a top down educational plan and select key players for advanced training, or bring on talented ITIL leaders with the experience to help make the transition as seamless as possible. Once the people in the organization grasp the positive potential of ITIL for the company’s growth, then the process begins to form a life of its own. This process cannot take place without the commitment to funding resources, time, and human resource development necessary to achieve success.

2. Assemble Powerful Teams

Taking the time necessary to build talented teams with leaders that have both the technical experience and the strong communication skills to articulate the overall vision and goals for the team is essential for success. The TWDC case study underscores the fact that successful ITIL adaptation takes time. Looking at the current role of IT in your organization and being clear about what you would like to see with regard to data management, reporting, efficiency, delivery, maintenance, etc. is vital to building a plan with clear goals and measurable outcomes. Selecting a team that will help you implement the changes necessary to reach your new profit and efficiency goals is easier if you are clear about what you want to truly achieve with the adoption of an ITIL infrastructure. Putting the time in to train and assemble strong leaders for the project will build overall trust in the process and will help safe guard against breaks in service and diminish other challenges as the project gets underway.

3. Keep Clear and Regular Communication a Top Priority

From the moment you start to generate the buzz, until the process is complete and running smoothly, communication is vital to keep all constituents informed and connected to the momentum of the project. Every leader needs to be fully fluent in best practice methods for communicating technical and non-technical aspects of the process to a wide variety of users. The message needs to be adapted to the recipient, not the other way around. Skilled ITIL leaders are aware that the CFO, the help desk worker, and the marketing manager have differing IT related roles and will need to hear about the ITIL infrastructure engagement process in a language that makes sense with examples that are relevant. Throughout the process regular updates and the celebration of milestones builds confidence in the process and builds a more cohesive team.

4. Strategically Build an ITIL that Serves Your Needs

The best part about the ITIL infrastructure is that it is flexible. You can integrate what works without “reinventing the wheel.” Taking time to collect insightful research gained from involving all teams who use IT in the beginning of the project will lead to a stronger ITIL implementation with less adjusting later. Strong ITIL leadership can ensure that the ITIL infrastructure will grow with you. A strategic ITIL infrastructure bolsters revenue by streamlining processes like ordering, and delivering products. Redundancy in storage is decreased thus speeding up servers. Everyday processes like scheduling, stocking inventory, maintaining client communication, performing system maintenance, and generating specific data to track progress in key areas, are all made more efficient, timely, and profitable with ITIL adaptation. Data security also increases with ITIL which can bolster client confidence. In order for ITIL to serve you well, you need to be clear about the realistic goals within your budget and then proceed if the resources are there to support the project.

A strong ITIL infrastructure generates the data that is needed to make maintenance and long term adjustments efficient, leaving more time for growing your business and leading your organization into new markets with confidence. Engaging in industry-wide best ITIL practice methods will help you build the strategy you need to assure you have a successful and integrated ITIL infrastructure with all teams in your organization driving the momentum forward to new goals and profitability. As e-commerce continues to expand and competition in the global market place increases, ITIL data can be a powerful tool to guide your growth. Case studies show that implementing an ITIL infrastructure can increase profitability; these case studies also reveal that the plan takes time, company-wide support, and team cooperation to succeed -three important factors to weigh heavily when considering an ITIL plan for you organization.

As I mentioned last time Erin works at Villanova University and this article comes from their new ITIL training course. This course is part of the overall IT Service Management training program.

How to Manage Chaos with an ITIL Framework

Andrew.Fryer — Tue, 03 Jan 2012 19:33:33 GMT

I have to confess I don’t know too much about the detailed mechanics of ITIL, but I couple of months ago I got chatting to an expert, Erin Palmer from Villanova University in the US, and over the Christmas holidays he’s written up a really good post by way of an introduction..

So you think you have IT chaos to manage? Imagine the IT services needed to tame the chaos of a multi-billion dollar conglomerate of 11 large-scale theme parks, two water parks, over 40 resorts, and a pair of cruise ships – and over 118 million customers annually. Did I mention these services also operate in all time zones across many languages and international borders every day all year long? Can you imagine an IT department of 1000?

These are the statistics from the Walt Disney Company (TWDC) case study as they took on adopting ITIL best practices in the mid-2000’s. Used since the 1980’s in the United Kingdom to manage the IT services of large governmental entities, ITIL has proven its value time and time again in a variety of large, medium, and small business settings worldwide. The IRS which processed over 236 million tax returns and collected more than $2.3 trillion dollars in revenue in the fiscal year 2009 uses ITIL, so does N.A.S.A., and a host of other business entities that aren’t nearly this big or complex. One of the reasons why ITIL structure continues to grow in popularity is that it is adoptable and flexible in nearly any business setting that uses IT to conduct its commerce.

Looking at recent ITIL case studies, several important points emerge when thinking about how ITIL might help you manage the IT chaos in your business setting, no matter how large or small.

1. ITIL processes are flexible and help manage the services IT provides as the business grows:

· Working with existing IT structures, ITIL grows the business efficiently

· Reducing redundant data storage, ITIL saves room on servers

· Centralizing data storage means increased security of company data

· Generating reports for monitoring progress is easier and more efficient with ITIL

· Increasing consistency and dependability is an ITIL goal; business is streamlined

· Maintaining the ITIL is efficient, resulting in less down time and “work-arounds”

2. ITIL best practices generates data to address IT and departmental problems pre-emptively

· Self-monitoring applications reveal areas that need attention before they become a problem

· Generating reports to track how various IT factors work together is easier and you can assess for glitches in the system before they cause a problem

· Measuring data and comparing goals across departments increases productivity and accountability

· Implementing company- wide IT standards and guidelines means less is overlooked, or repeated and that there is increased communication and cooperation among departments regarding company goals

3. Client Trust increases

· Less outages and down-time due to IT issues means increased client satisfaction

· Increased data security is a strong selling point

· ITIL provides strong back-office support which positively impacts the user’s experience

· ITIL means streamlines services that lead to faster response time when a client needs assistance

4. The ITIL structure helps team building

· When company goals are ubiquitous across all sectors progress is easier to monitor

· Each team plays a unique role in reaching company goals and can keep track of contributions toward the goals

· Teams know that company-wide standards are in place so everyone is on a level playing field when striving for a goal

· Reports are easy to generate and share regarding company-wide progress

· Communication between teams and with team leaders is more streamlined so response time can be faster when team members request changes or observe a situation where an IT adjustment would streamline services even more

· Help desk requests are processed more efficiently and data tracks trends that need more careful attention

As a business grows, the chaos of IT grows as well. By implementing an ITIL, not only can you harness some of that chaos, but you can make it work for you. By analysing the huge amount of data available to you in the ITIL framework, more specific reports are possible that indicate progress toward goals. Team building within the company is increased through the implementation of standard language, goals, and processes from one department to the next. Increased efficiency can mean increased profits, a more enjoyable IT experience, increased client satisfaction, and more “free” time for managers to promote company growth and increased profitability.

Even though your company goals may not involve IT that provides a literal thrill ride for your clients, through examining case studies of companies that have successfully adopted ITIL best practices, you may just find that the lucky star you were wishing for is closer than you think. With its internationally recognized standards of best practice, ITIL offers the possibility for unlocking greater potential and momentum for increased growth, efficiency, security, and profitability in your company no matter where you are and what service or product you provide. Chaos in the global marketplace will continue to increase; ITIL offers a proven method for harnessing the chaos and riding it all the way to the bank for those willing to put the time in to prepare, invest in, and utilize an ITIL framework to its fullest potential.

This article was submitted by Villanova University’s new ITIL training course. This course is part of the overall IT Service Management training program.

Merry Christmas and a Happy 2012

Andrew.Fryer — Fri, 23 Dec 2011 10:29:26 GMT

My Christmas card for 2011 is inspired by a frequent visitor to my back garden the Goldcrest. The trick to seeing them is to resist the temptation to tidy up and dead head too much, whereas that’s exactly the sort of thing you should be thinking about in your data centre to keep it clean and tidy. The problem here is often knowing what you’ve got, and why it’s needed especially with an explosion of virtual machines. So before you hit the delete key I would suggest you download and run the Microsoft Assessment & Planning Tool (MAPT). This not only reports on Microsoft stuff it looks at 3rd party software and your hardware and virtualisation environment.

I will certainly be doing lots of tidying up on my demo rigs to free up enough resources for System Center 2012 now much of it is at release candidate and of course the release candidate of SQL Server 2012 is now available as well. The team have planned out lots events next year, IT camps, the System Center 2012 preview tour is still on tour and of course I’ll be at SQL Bits to complete my perfect attendance.

Until then, whatever you’ll be doing over the Christmas break, have fun and don’t hog the XBox.

Andrew

Virtual machine density in your data centre

Andrew.Fryer — Tue, 20 Dec 2011 10:55:23 GMT

I can only run 11 server based Virtual machines on my laptop, but all bar three of them are running SQL Server:

3 x VMs running SQL Server 2012 beta and the new AlwaysOn Cluster. Note one of these is running SQL Server 2012 on Windows Server Core
1 x VM running SQL Server 2012 the database engine plus 3 x instances of analysis services, master data services and data quality services not to mention SharePoint with Office Web Apps enabled.
1x Windows 7 VM with Office 2010, Visual Studio 2010 ultimate and all the System Center client tools, remote server administration tools
2 x System Center Service Manager 2012 beta VM’s one fore the service and one for the data warehouse
1 x VM for System Center Orchestrator 2012 RC
1 x VM for System Center Virtual Machine Manager 2012 RC together with then new System Center App Controller
1 x VM for System Center Operations Manager 2012 beta
1 x VM for Red Hat Linux
1 x VM as my domain controller and DHCP server

The limiting factor I face is RAM - the minimum memory requirements of many of the System Center tools limits what I can cram into to 16Gb, but dynamic memory is a great help here. Anyway it’s a fair increase over the four VM per server density that was discussed when Hyper-V came out. That ratio of virtual to physical can of course be pushed much harder on ‘proper servers’ designed for Hyper-V rather than my laptop mash-up. A good example of this was the labs run at various big events like the Microsoft Management Summit in May where they were able run 225VMs per host although with 128Gbs or RAM they would only be getting a basic 512Mb per machine.

However there is another way and that’s what Microsoft does in its newer data centres, like the one I visited last week. The whole data centre runs on a modified Hyper-V but what’s different is that there are thousands of low cost basic servers rather than hundreds of huge monsters. Blogging in more detail about how these work is more than my job’s worth so if you want to know more then the Global Infrastructure Services site is the place to go (there’s a video tour of one of the data centres here) . However what I can say is that all the lessons learnt from operating at this scale are then put into the next releases of Hyper-V and System Center, for example:

the bare metal host provisioning in Virtual Machine Manager 2012
the separation of duties in Virtual Machine Manager 2012 where the team who look after the physical servers don’t control the services that run on those servers, that’s down to the application teams.
the integration of AVIcode into Operations Manager 2012 to understand what problems are affecting the applications themselves.

So if you want to get an idea of how to run a data centre at scale then you’ll want to spend your downtime over Christmas learning virtual Machine Manager either by watching the new content on the Microsoft Virtual Academy or by pulling down the Release candidate (which you can install or uses a preconfigured hyper-V virtual machine)

IT Camps

Andrew.Fryer — Tue, 13 Dec 2011 12:37:15 GMT

We aren’t all the same, we learn differently, we work in a wide range of business that have very different needs, and we learn at different speeds. So spending a day being lectured to on technical stuff, isn’t going to be the right answer for everyone and no matter how good the speaker is he has to tread a middle line to keep the experts in a topic interested while ensuring those new to it aren’t left behind. Talks on overall positioning or to show off some new cool stuff work well, but if you want to know how to get stuff done and get inside the technology, then a different approach is needed.

So Simon May and I have cooked up a different style of event, an IT camp, where the content is driven by the audience, but within a general topic areas. We wanted to test this out by running a limited public beta, so we invited a select group of IT professional guinea pigs to a day in London to test the idea. We thought a basic day of clustering and server virtualisation would have the broadest appeal as Hyper-V is being more and more widely adopted.

One of the problems with this kind of unstructured event is that we didn’t have the usual pile of PowerPoint decks as hand-outs, Simon manned the whiteboard and I manned the keyboard. One thing I thought would be useful would be to share some of what we built as we plan to run a lot more events like this next year..

My rough guide for installing Hyper-V server and adding it to a cluster with iscsi storage is up on skydrive ..

and the resources we used were:

Software:

Hyper-V Server 2008 R2 with sp1 is a lightweight (based server core for those that know it) edition of Windows Server that is not licensed and can only be used to run Hyper-V.
Windows Server 2008 R2 with sp1 Evaluation edition If you aren’t comfortable using the minimalist interface in Server core then you could use this and enable the hyper- role on it. You will also need it to create some VM’s with
Core Configurator is a PowerShell based interface to make Hyper-V server and server core easier to manage directly than it is with SConfig
iscsi target software allows you to emulate shared storage using Virtual Hard Disks (.VHD)
RDCMan allows you to control a lot of remote desktop sessions
Zoomit (part of Windows sysinternals) allows you to pan and zoom around your screen when presenting
System Center Virtual Machine Manager 2012 manages large server virtualisation deployments and is now in release candidate. There’s also a prebuilt VHD which can integrate into your test sandbox / demo rig here

Learning resources

Microsoft Virtual Academy

Installation and setup guides..

Hyper-V Survival Guide on TechNet , this has sections on dynamic memory networking, clustering with iscsi and just about anything else you’ll need

Our IT Camp guinea pigs seemed to like the event but also gave us a lot of helpful feedback which will be baked into the next events we do, so keep an eye out for IT camps coming to a location near you in 2012.

NO SQL Server

Andrew.Fryer — Sat, 10 Dec 2011 11:10:14 GMT

I spent last Thursday at Black Marble’s Architect Forum and my slot was on SQL, NoSQL, some SQL. It went down very well but I used OneNote on my tablet not PowerPoint and like all modern art my drawing needs a bit of explaining..

Databases were originally used to store transactions which are highly structured, lots of little fields grouped together in tables which in turn had hard relationships plus an environment where a the transaction was either committed in its entirety or it was rolled back. These simple transactions got more complex and other kinds of data got associated with the transaction, the contract, a picture of the product and so on. Nowadays all sorts of stuff get thrown into databases like SQL Server, for example all the content from a SharePoint site. SQL Server has evolved to make this a lot easier and a lot more useful with technologies like FileStream, and the new FileTable in SQL Server 2012 so that the contents can be used as they are still individual files while remaining part of a SQL Server database (in a special file group) . The point of storing all this data is to be able to retrieve it, and if those unstructured files have text in them then they can be included in a full text index so the contents can be searched as well as the metadata about the file.

The next thing to consider is how are we using the database:

I have already mentioned transactions and content management, and two other key uses are business intelligence and as a backend for web sites. This extended usage doesn’t really cause any problems in itself until we consider the users:

The Developer, don’t like SQL and this phobia started the No SQL movement which has changed to be Not Only SQL so (NOSQL). You don’t have to use SQL to develop with there is entity framework and link to bridge the gap between the relational world I know and the object world of the modern developer. You might need SQL to create objects or modify them and of course keep that under source control, however the SQL Server Developer Tools mean that it’s not necessary to get your hands dirty if you don’t want to. Another problem for corporate developers is the need to hand of the project top the IT department for deployment as developers are rarely allowed access to a production environment. so there’s the Data Tier Application for that which wraps up all the database code for deployment by the DBA.

The DBA. The challenge of SQL Server for the modern DBA is that there can be quite a lot of databases which are only getting larger and many businesses don’t have a dedicated DBA anyway. So there’s extensive tooling for managing lots of database servers using Policies (like there are policies in Windows) and PowerShell support which should be familiar to a part time DBA and allows scripts to act on SQL Server as part of a bigger script to provision virtual machines create logins in Active Directory etc.

The Information Workers (IW) . This is Microsoft speak for the end user and they should not have to learn SQL. They should also be insulated from having to know too much about the detailed data structure of the systems they want to analyse and report from, so in many BI solutions there’s a semantic layer to allow users to drag and drop data without understanding the SQL or the relationships. Having said that the power users often do have some of this knowledge and they do need to understand how to join sets of data together. In PowerPivot 2012 for Excel these users actually create an entity framework which they can then share with their less technical colleagues either via SharePoint or as a BI Semantic Model deployed from analysis services in SQL Server.

There’s a bunch of technical developments which have helped to keep databases in general and specifically SQL Server relevant and fast while data volumes continue to explode:

Column based indexing rather than storing data in blocks of rows gives great read performance, the indexes can be seriously compressed which then means its possible to cache them in memory for further performance increases. solid state storage just make things run faster with no need to change the design of a database but their expensive and possibly less resilient so a good first step is to use them for caching e.g. to put tempdb on them while the actual database still resides on a SAN behind a cluster.

I have covered off FileTable above and spatial is actually a type of structured data which I should have drawn on the left, however it worth noting again that this isn’t just about storing that data it’s about having a rich set of functions to query the data and fast indexing to ensure the queries run quickly .

The current hot topic is Big Data, the ability to store anything and everything without worrying to much about structure. What's important here as with any data store is the ability to search it, analyse it and make decision from it. In an agile world this needs to be done quickly and by the business not the techies who understand the complexities of that data. So while it’s nice to see that Hadoop is going to run on Azure to store mountains of data what interests me is the tooling that will be put into Excel to make that store of data useful and directly accessible to the user.

Another problem for a database is velocity; the ability to be able to react quickly to incoming data and make decisions from it. Stream Insight is actually not really anything to with SQL Server in that you don’t need a database to use it but it is part of the product, and there are occasions where the end product cold be a feed into a database so I think I can include it here. It is a set of classes to proceeds feeds of data near real time (i.e. sub one second) by aggregating it and raising events off of it to other systems. It is a sort of real time map reduce and uses LINQ so no actual SQL is required to code it either.

Putting all this together there we have Not Only SQL Server (NO SQL Server ?) databases an ecosystem around the actual database engine which is still part of the platform in SQL Server, where you can elect how to store and process data using familiar tools in unfamiliar ways, to create information and insight. Most of the stuff I have mentioned here is doable in the current version (SQL Server 2008 R2) however File Table is new for SQL Server 2012 and PowerPivot has been enhanced to work with the new BI semantic model so you’ll need to look at the current beta if that sounds interesting

Private Cloud–Nothing to see please move along

Andrew.Fryer — Wed, 07 Dec 2011 08:14:42 GMT

One of the reasons the term Private Cloud is getting a bad press is that it’s all marketing fluff and isn’t real. In any data centre you are going to be doing thing like..

deploying applications
fixing applications
performance tuning
load balancing
resource planning
decommissioning applications
patching and maintenance
backup! …and you might also need to do a restore on occasion
audit and compliance
bid for more resources from management

This list isn’t much different to what I used to do as a Unix admin back in the nineties, however how this stuff gets done is now totally different; I used to send out patches & fixes on CD out to branch offices, and had to visit these offices to setup TCPIP. If a server or desktop died rebuilds were tortuous and painful and if that server had an application on then we would have to reinstall on another server and break out the backup.

Later on we could cluster servers but this was painful and expensive and only a few services, like SQL Server, could failover properly.

Virtualisation changed things a lot, but I feel this was a bit like moving to a bigger house; you pack up everything and get rid of a lot of clutter, however a year after you moved in all the extra space has gone and in some cases there is more mess than there was before. What matters in a post virtualised world is how much effort is required to manage those virtual machines This takes me back to another old discipline; systems analysis - every entity needs a process to create , read update and delete (CRUD) and this should apply to VMs as well as to data stores. Applying CRUD to VMs means that there should be processes in place to

Create. Use a self service portal or via a service desk request. Another Private cloud scenario is that they might be created automatically to meet demand when a service gets busy

Read. Access them and continuously monitor them to ensure they are healthy

Update. Apply fixes and patches to keep them current

Delete. remove them when they aren’t needed any more, the service they are providing might be scaled back or it may be that the whole application has been superseded

None of this is new to the public cloud vendors, Amazon, Google, Microsoft etc. this is what their data centre staff have setup long ago for their online services like Mail, Search, and shopping. What is new is that the best practices arising from doing this at scale (e.g. one data centre admin per 1-2000 VMs) are being built into software like System Center 2012 so you can operate you own infrastructure as efficiently. For example patch management is automatic, a new VM is a mouse click, and you are fixing the problem before the user realises there is on.

So to quote from that well known group of IT pundits the Fun Boy Three/Banarama “It ain’t what you do , It’s the way that you do it .. and that’s what gets results”.

BCDBoot–one of my new best friends

Andrew.Fryer — Tue, 29 Nov 2011 13:36:55 GMT

I am getting ready for a series of interactive events next month and one of the things I wanted to show was a hyper-V cluster. No doubt Simon and could have blagged some kit, but we decided to use what we had lying around in the office. This meant I had to muck about with my shiny Orange Dell Laptop so I eventually decided to dual boot so that I could run my old demos or use the laptop for these new events.

Currently when I run my demos I am actually booting to a VHD running windows Server 208 R2 with the hyper-V role enabled, not that boot to VHD is not virtualisation , you are just using the VHD as your hard disk. This means you can back up the environment by just copying that VHD. What I wanted to do today was to setup another VHD for these new events and the easiest way to do that is to break out of the windows server installation process by hitting control F10 to bring up a control screen and then use DISKPART to create and attach a VHD which you can then install the operating system to. you’ll enter command like this..

select disk 0

select partition 1

list volumes (to see which drive you want to create the VHD on)

create vdisk file=”c:\deepfat.vhd” maximum=20000 type=fixed

select vdisk file=”c:\deepfat.vhd”

attach vdisk

exit diskpart and refresh the list of volumes and you’ll see the vhd you just created and then you can select it and install to it (this work in Windows 7 or Windows server 2008 R2). The nice thing about this is that it creates a boot entry for it. However if the worst happens and your disk dies and you want to rest your environment on a new hard disk what do you do? In my case I had my first boot VHD on a volume I wanted to combine with another so I needed a simple way to put the VHD on a volume and then get an entry for it in the boot menu so I could boot from it.

If you have used BCDEdit by itself you’ll be aware of how fiddly it is and if you haven’t trust me. However Simon pointed out a related command BCDBOOT which is a lot simper to use..

simply mount the VHD so it appears as a drive letter (

select vdisk file=”c:\deepfat.vhd”

attach vdisk

assign letter v

and then run bcdboot to add an entry for it ..

bcdboot v:\windows

the only problem you might end up with (and this happened to me) is that the description for each entry will be the same so you won’t know which one to select at boot time. You’ll have to use BCD edit for this by running bcdedit with no arguments look for the bit of the output that has the entry to your new VHD and copy its GUID to the clipboard. Now you can run

bcdedit /set (paste your GIUD here) description “meaningful description in double quotes

Update [1/12/11]

Having made these changes my original demo rig wouldn’t run hyper-V and it turned out this was because of the boot store (which is what BCDEdit writes into), so I also had to run another BCDEdit command to start the hyper-V service as part of the boot process..

bcdedit /set "{default}" hypervisorlaunchtype auto

the {default} refers to the default entry in the boot store which in my case is my demo rig, you could of course specify the GUID for a particular entry.

Hopefully this is useful but to be honest I wanted to post it so I could refer back to it later.

SQL Server 2012 – TSQL

Andrew.Fryer — Wed, 16 Nov 2011 11:39:00 GMT

I have to confess I hadn’t paid too much attention to the changes to T-SQL in SQL Server 2012, and it was only a question at NextGenUG in Abingdon last night that prompted me to have a look. It turns out there are some really useful new commands, which I could certainly have done with when I was allowed near production code. So here’s what I found:

TRY_CONVERT(). If I had a penny for every time I feel foul of trying to convert to dates or numeric from random string data I had loaded up in my BI projects… Anyway this is better solution than ISNUMERIC() and ISDATE() and typically looks like this

SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result

returns 2010-12-31 00:00:00.0000000

SELECT TRY_CONVERT(datetime2, '11/31/2010') AS Result

return NULL

so the same syntax as Convert, and you don’t have to do an initial test but you’ll want to include additional processing to handle the NULL when TRY_CONVERT() fails.There is also a new PARSE() & TRY_PARSE() which deal with converting dates and currency formats across different locales

FORMAT()

differs from CAST() and CONVERT() by providing localised output from localised input from a different locale e.g.

with the Current date is 15/11/2011 with my machine set to a locale of EN_GB

DECLARE @d DATETIME = GETDATE();

SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS Result

returns 11/15/2011

so the same date but in US format

Note This uses the CLR (like HierarchyID and other newer data types) but it doesn't need to be turned on:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

DATEFROMPARTS() builds a date from separate variables from year month day..

SELECT DATEFROMPARTS ( 2010, 12, 31 )

returns 2010-12-31

This is one of a set of functions to build up dates and times to various formats e.g TIMEFROMPARTS(), DATETIMEFROMPART()CHOOSE() allows you to specify which item to pick in a list of items e.g.

SELECT CHOOSE( 2, 'IT Professional', 'DBA', 'Developer', 'Tester' ) AS Result

returns DBA

IIF() works in the same way as in Excel – IIF(condition, value if true, value if false)

CONCAT () joins strings together to one output e.g.

SELECT CONCAT ( 'DEEP', 'FAT',’’,’FRYER’, NULL, 50 ) AS Result

returns DEEPFAT FRYER50

Note: the output data type will vary according to what is put in and if one of the inputs is NULL then CONCAT() will just ignore the NULL value and join the non NULL values togetherTHROW allows you to raise an error in a TRY.. CATCH block e.g.

THROW 51000, 'we have a problem Houston.', 1;

OFFSET..FETCH

This is a modification of the ORDER BY clause in a SELECT statement..

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID

OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

This allows to you to limit which part of the results are returned based on the ordering clause

SUMMARY

This isn’t an exhaustive list of all this the new T-SQL commands there is other new T-SQL for accessing the other new features in SQL Server 2012 e.g. table valued functions for semantic statistical search, file table, security enhancements etc.

Finally these new commands are all in the current beta of SQL Server 2012 (RC0), if you want to try them out.

SQL Server 2012 – Always On

Andrew.Fryer — Tue, 15 Nov 2011 08:49:38 GMT

There have always been several ways to do high availability in SQL Server, but choosing the right one has always been difficult as each approach has obvious benefits coupled with unavoidable limitations:

Clustering looks after a whole instance of SQL Server containing many databases and is completely transparent to an application. However shared storage adds cost and complexity and there is only the one copy of the database(s) on that shared storage.

Mirroring creates a continuously updating replica of a given database, failover is really fast and it’s easy for a DBA to setup. However mirroring has several significant limitations:

A special connection (SQL Server native client )is needed to mirroring so not all applications can work with it
Protecting multiple databases so that if one fails they all fail over is not really possible.
There is only one mirror of the database
The mirror is not directly usable it just sits there unless you are prepared to work with snapshots.

Log shipping is sort of manual mirroring which allows more than one replica to be kept; perhaps a local one and a remote one. This is more difficult to setup and failover is not automatic you have to reset all of this yourself.

To build a better SQL Server mousetrap, you would want a solution that:

Looks like a cluster to any application i.e. there is a DNS entry to the cluster to which the application connects without ‘knowing’ which node SQL Server is running on
You would want to treat a group of databases as an object so that they can be failed over etc. as needed in one go.
As with log shipping, there wouldn’t just be one other node behind the primary there would be multiple mirrors/secondaries
The mirror could be read only and therefore available for reporting
You could opt to have some nodes connecting asynchronously and thus have a remote replica of your databases without needlessly slowing down the primary.

Up until know that meant that we would have use more than one feature in concert e.g. mirroring and clustering together to achieve the high availability we wanted. What SQL Server 2012 AlwaysOn does is to provide this combination in one single feature:

It uses the Windows Failover Cluster feature in Windows Server but doesn’t use any shared storage. A normal install of SQL Server 2012 is then done on each node and the SQL Server 2012 service is then configured to use the cluster..

Having done that you then tell the SQL Server service on each node to use the cluster the new AlwaysOn High Availability tab in the properties for the service..

However AlwaysOn is actually doing something very similar to mirroring under the covers, in that there are replicated copies of the databases being protected not just one copy on shared storage as there is for clustering – and AlwaysOn doesn’t need to use shared storage. You’ll also notice that for databases to be protected by AlwaysOn they need to be in full recovery mode and backed up (preferably to a share that’s visible from the other nodes). However with AlwaysOn you can have multiple secondaries and you create availability groups, which are sets of the databases you want to keep together.

There’s a wizard in SQL Server Management Studio for this where you can specify the nodes, the databases and the options for accessing each node. Note this uses TCPIP ports like mirroring does (so port 5022 by default) and these need to be opened in the firewall for this all to work.

There’s a dashboard to confirm all is well ..

There is also an option to create a TCPIP listener which provides an address and DNS entry for the cluster. If you set this up you can connect directly to the cluster from any tool that can connect to SQL Server, in this case I have connected to the TechNet cluster from management studio in the same way I would connect to any other instance or cluster..

However you can also connect directly to the primary or secondary as well and for a read only secondary that’s how you would do reporting.

I have a short (8 min) AlwaysOn screen cast if you want to know more or have a guide to help you try it yourself.

Finally be aware that this is not replacing clustering, mirroring or log shipping but it is only going to be available in SQL Server 2012 Enterprise edition.

Anti-Virus and Hyper-V, Yes or No?

Andrew.Fryer — Wed, 09 Nov 2011 09:52:00 GMT

The parent operating system in Hyper-V is windows server and it’s a relatively simple matter to install your standard anti-malware tools on it, but is this a good idea? You can in fact install all sorts of applications and roles in the parent/physical operating system, and the guidance from Microsoft for production environments is not to. However this article doesn’t advise for or against installing anti-virus it just tells you what to do if you decide to implement it.

In this post I wanted to give you my thoughts on it so you can make an informed decision

The case for not installing anti-Virus

Anti-Virus is one part of a suite of processes and technology to ensure your applications aren’t corrupted or prevented from working. Assuming this is a high priority, you’ll want to also consider the following:

Ensure your hyper-V servers are constantly kept up to date with the latest patches. clustering a and live migration mean that your guest virtual machines should never have to be off line while this is done.
User Hyper-V server or a server core installation for Hyper-V. This has a much smaller attack surface, e.g. there is no browser or graphical interface, and cuts patching in half.
As mentioned above don’t run anything else at all in the parent operating system, not even additional server roles and features.

Having done all of that what exactly is the anti virus going to check for? It can’t protect against zero day attacks, and it can’t be set to monitor the virtual machines files (VHDs etc.) and services associated with hyper-V as this will cause it to fail. Note you will certainly have anti-virus agents running in the guest virtual machines to protect them.

The case for installing anti virus

You have done a detailed risk assessment and have established that in your own environment there is a need for anti-virus alongside hyper-V.

The most common argument in favour I hear is that it is company policy, and even though that was not made with Hyper-V in mind you may have no alternative but to do so.

Summary

What I would not recommend, is doing this or not doing this just because you read it on a random post, or picked it up as hearsay – make in informed decision as you would for anything involving the security of your production infrastructure

Finally If you do decide to implement anti-virus alongside hyper-V the exclusions you’ll need to make for Hyper-v to work are here, and you may also want to refer to Microsoft’s best practice for securing Hyper-V so you don’t even have to take my word for this!

SQL Server 2012 editions and dependencies

Andrew.Fryer — Tue, 08 Nov 2011 14:03:00 GMT

You may have seen that there are going to be some changes to the way SQL Server 2012 is licensed and the editions available, if you haven’t then the detail is here. What I want to cover off in this post is what the various new features depend on so you are clear on what else you need besides the new licenses of SQL Server 2012.

SharePoint

The three components of BI in Microsoft are SQL Server, Office and SharePoint, and this has been true since the integration of Performance Point in SharePoint 2007. SQL Server 2008 R2 add PowerPivot to SharePoint to allow excel power users to share the analytical mashups they created with their colleagues. This relied on Excel Services in SharePoint enterprise to create a PowerPivot gallery where other business users could slice and dice the PowerPivots created by their more technical peers.

SQL Server 2012 adds in two new BI features, Power View and Report Alerting in the new Business Intelligence edition and in the top end Enterprise edition:

Power View (formerly known as Project Crescent) is a new ad hoc reporting tool that builds on PowerPivot in that its reports are designed to be previewed in the PowerPivot gallery and so it is also reliant on SharePoint Enterprise edition.
Report Alerting allows users to put a rule on any report and be notified when the rule is met. The rules are defined in a simple interface can reference any value or field on the report. This capability is only available if Reporting Services is in SharePoint integrated mode. However that only requires SharePoint Foundation edition.

Also not to get these new features to work you’ll need SharePoint 2010 sp1 or later

Windows Server

One of the other, if not the, top feature in SQL Server is AlwaysOn. This builds on windows failover clustering services (but with no shared storage necessary) to provide mirroring like functionality across multiple databases with multiple secondaries which can be read only if necessary. This feature is only available in SQL Server Enterprise edition and because it relies on failover clustering it will only work on Windows Server Enterprise editions and above (as per this editions datasheet).

As for which version of Windows will run SQL Server 2012, it’s the same as for SQL Server 2008 R2 - Windows Server 2008 sp2, and on the desktop Vista Sp2 or later. I am guessing Windows Server 2008 is still supported as this is the last 32 bit server operating system but clearly the clock is ticking for 32 bit server support so you’ll want to start thinking about removing that as a dependency in your infrastructure