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

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.

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..

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.

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.

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!

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.

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.

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.

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

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)

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.

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

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”.

Further reading:

 Microsoft Virtual Academy (which now has a separate module on System Center Virtual Machine Manager 2012).

The System Center 2012 road show touring the country:

• Birmingham on 17th January (register here).
• Edinburgh 28th February (register here).

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.

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.

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.

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!

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 

System Center Operations Manager (SCOM) 2012 is now in beta along with all of the other System Center 2012 suite, and because it’s a beta the installation process is a little tricky. Having spent a morning on it I thought it would be good if I told you what I did so you don’t have to go through this pain yourselves.

The hardest part of the installation is setting up SQL Server for SCOM and realistically you are going to need to dedicate an instance of SQ Server to SCOM as it uses a legacy collation order which you won’t have enabled on other instances and may not be able to change.

SCOM needs the database engine, full-text search and optionally reporting services..

When you get to the server configuration screen,ensure that the browser and SQL Server Agent are set to automatic startup (you can fix this later if you need to in SQL Server Configuration manager)

The the really important bit, click on the collation tab ..

and select the Latin1_CP1_CI_AS.  If you don’t do this SCOM can’t use the instance and you are probably best off uninstalling and reinstalling. the command line to install SQL Server like this is..

SETUP
/QS
/ACTION="Install"
/FEATURES=SQLENGINE,FULLTEXT,RS,CONN
/AGTSVCACCOUNT="[your account]"
/AGTSVCSTARTUPTYPE="Automatic"
/ISSVCSTARTUPTYPE="Automatic"
/ISSVCACCOUNT="NT AUTHORITY\NetworkService"
/ASSVCSTARTUPTYPE="Automatic"
/ASCOLLATION="Latin1_General_CI_AS"
/SQLSVCSTARTUPTYPE="Automatic"
/SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
/SQLSVCACCOUNT="[your account]"
/SQLSYSADMINACCOUNTS="[accounts with sys admin privileges]"
/SECURITYMODE="SQL"
/TCPENABLED="1"
/RSSVCACCOUNT="[your account]"
/RSSVCSTARTUPTYPE="Automatic"
/RSINSTALLMODE="DefaultNativeMode"
/FTSVCACCOUNT="NT AUTHORITY\LOCAL SERVICE"

Having SQL Server installed you’ll then need the  .Net framework 4

If you want the the web console you’ll then need to turn on the IIS role in Server Manager.  If you miss a few options here the prerequisite installer will tell you what to add, but won’t do it for you!

Finally If you want reporting turned on you’ll need to install the reporting services report viewer package as well.

Having done all of that you can then start the install.  I did a one Virtual Machine setup and went with the defaults although I do use dedicated domain account for all my System Center services.

If you haven’t installed SQL server as above then you wont be able to select it in this step of the SCOM installer..

(Note if you went with the default instance then you should enter servername\MSQLSERVER and leave the port at 1433)

You’ll get the same screen again for the data warehouse that SCOM will use for reporting.  If you installed reporting services and you selected the option for SCOM reporting then you should get past this screen OK and you’ll see it set like this

The final thing to be aware of is to use the right accounts for SCOM, I have a dedicated account (contoso\scservice) I use for my demos..

The new console looks very similar to the old one, but there are numerous changes..

and there is also a shiny Silverlight based web console to avoid having to deploy the full fat version to delegated users to see what’s gong on

In subsequent posts I want to start to show you that there is actually quite a lot to the new version.

Putting up a marketing slide that says SQL Server is a good private cloud citizen is good marketing, but what’s in the box to back the claim up?

My top three would be:

Windows Server core.  Patching is a major maintenance problem with lots of virtual machines, and server core cuts that in half.  Getting the most out of the physical server is also important and with windows server core being the bare minimum of windows needed to run SQL Server then that leaves more resources for SQL Server.  If you want to check this out I have a series of posts on getting it going.

Always On which combines clustering and mirroring to create a highly available set of databases (an Availability Group), without the need to muck about with shared storage which is not recommended by Vmware and only works with i-scsi storage on Hyper-V.

Contained Database Security, means that moving databases around doesn’t also require digging out the associated logins from the instance and then stuffing them into the instance where you are moving the database to.

This is on top of two other private cloud friendly features in SQL Sever 2008 R2:

Image prepare and Image complete allow SQL Server to be installed (prepared ) on a VM which can then be used as a template for example in System Center Virtual Machine Manager. Part of creating these templates is sysprep so that when a new VM is created from it it will have a unique SID for Active Directory.  SQL Server doesn’t like sysprep but image prepare gets around this by doing a preinstall which can then be completed from a script once the VM has been sysprepped, named and joined to a domain.

Data Tier Applications.  This allows the design of a database to be abstracted from SQL Server, and allows the database to be moved as part of the new Server Application Virtualisation feature in System Center Virtual Machine Manager 2012 and to move the database to SQL Azure i.e. the public cloud.

I will be going into these into more detail in the coming weeks, now I am back from my travels, but in the meantime, if you want to do your own evaluation, there are three things you’ll need to get started, exploring these features.

• The SQL Server 2012 beta (ctp3)
• The SQL Server 2012 Product guide
• The SQL Server 2012 Developers Training Kit

The Accountants, sales guys and lawyers in your business don’t understand or care about how fast your hypervisor is or how many nodes there are in your cluster. What they care about is that the e-mail works the intranet is up and the business can trade with its customers. In short they care about applications, and they are paying you so they don’t have to worry too much about them.

Applications come in all shapes and sizes from a single install to the sort of server farm used by SharePoint, they can be written in house or bought in from  a vendor (ISV). So how is it possible to understand them all and keep them all running?

               
Let me answer this and the seemingly unrelated question: “Why are there so may tools in System Center?”

Are your applications working?

You could answer this by repeatedly trawling around all the event logs in your servers either manually or with the mother of all PowerShell scripts, but you need to know about problems, before your users do or at least as soon as they do.  A simpler black box approach would be to create a synthetic transaction against a key application e.g. to replicate a user logging into a site and opening a page, and then test how long this takes once every 15 minutes to check its working as planned.  This capability is built into System Center Operations Manager (SCOM) today and while it will tell you there’s a problem it doesn’t really tell you what’s going on under the covers unless there are good errors in the event log from the application. A new approach in SCOM 2012 is to peer inside the code and pickup issues coming from calls in the application code and show performance problems and errors in a simple interactive interface. This built in capability comes form an acquisition, AVIcode, so actually that’s one less tool you’ll need to stay on top of things.

Managing the application

It is important to understand the dependencies on an application e.g. database, web service and of course compute network & storage, especially if this is all running as virtual machines.  So in several of the System Center tools you can now diagram what these services look like in Virtual Machine Manager and in the new App Controller.  Templates in SCVMM let you scale these services or deploy more of them and a change to the template is automatically reflected in the instances created from it.

Moving the application

Application virtualisation , the business of separating the application from the operating system has been available for some time for desktop applications. However server applications are often multi-tier and also run as services, so delivering server application virtualisation is harder and is only now part of Virtual Machine Manager 2012. The clever bit about this is that you can deploy your non Microsoft developed app (e.g. java, PHP) onto a windows virtual machine that could be running on XEN Server, Vmware or even Hyper-V.

Joining the dots

The days of changing things at random in a data centre are largely gone, what is needed today is extreme automation and standardisation coupled with a set of standard operating procedures.  While PowerShell can certainly do this, interop with lots of disparate systems can be hard, event handling is rudimentary and the scripts can be hard to debug and maintain.  The processes are much better designed in Orchestrator as this has deep integration to the key vendors in systems management, it is very visual and easy to understand and it can be driven from or drive to your help desk, be that Service Manager, Remedy or similar.  This then ensures that all changes are properly tracked and all processes are consistent.

Back Up Plan

I often get told off  for not mentioning Data Protection Manager, not by the Microsoft sales team but by its loyal fans at events I go to. This is because it backs up all kinds of things in the data centre from VMs to databases, SharePoint sites and Exchange but intelligently so that it understands them and can restore individual documents, mails etc.

All of the System Center 2012 suite is still under development at the moment with most of it available as a public beta:

• System Center Virtual Machine Manager RC
• System Center Configuration Manager Beta
• System Center Operations Manager Beta
• System Center Orchestrator Beta
• System Center Data Protection Manager Beta

and you can get all of these from the System Center Evaluation Center.  However if you want to see this stuff in action rather than install it yourself then there’s a System Center 2012 road show coming to a city near you..

There is a lot of advice on whether to virtualise SQL Server and many dbas are under pressure to do this.  There’s good advice here from SQL CAT on best practice on hyper-V and if you are in the business of acquiring hardware to run SQL Server virtualised then there are two reference architectures that are relevant depending on how many databases in your organisation:

Hyper-V Fast Track is a generic hyper-v solution available from several hardware vendors including HP designed to maximise generic workloads running on Hyper-v, including SQL Server.  Up from this you might consider putting all your SQL Server virtual machines on one optimised server and HP have their Database Consolidation Solution for this.  Like the Hyper-V Fast Track, this is preconfigured with System Center Virtual Machine Manager (SCVMM). What makes it different is that there are ready to use template SQL Server VMs of different sizes which you can then quickly deploy.  

However there comes a point when a SQL Server database is so large and so widely used that it is consuming all the resources of a physical server an obvious example of this is business intelligence.  Rather than work out how to set this up there Fast Track reference architectures and appliances for this.

The Microsoft Fast Track Data Warehouse reference architecture built into the HP Business data Warehouse provides for data warehouses of up to 5Tb of compressed data. It has a bigger brother the Parallel Data Warehouse  (available from HP as the Enterprise Data warehouse and also from Dell) and this scales to 500Tb, but does so by using multiple physical servers which behave as one.

The other interesting appliance is the is the Business Decision Appliance which is specifically configured for PowerPivot for SharePoint as this can be a little tricky to set up and even if you have a SharePoint 2010 farm you’ll probably want a dedicated server for PowerPivot if it is to be used widely in your organisation. So this appliance has SQL Server enterprise with all the BI components installed plus SharePoint 2010 enterprise with all the integration configured for you. All you have to do is to use the HP web config tool to join it to your network domain and you are ready to use it.

So these appliances might seem a little bit different from a scalable elastic private cloud, and that’s because what you typically want from a database platform is predictable performance.

Yesterday I spent a great day at the Leeds Virtual Machine User Group, with attendees varying from the IT Professionals from most of the cities law firms, to local universities and colleges where both the IT guys and the students took time out to attend, so a great talking shop and the subject of IT education came up.  The students I spoke to seemed very keen to learn  about the IT Professional world despite that not being the focus of their degree and on the employer side there were many there who felt that industry certification at the right level was more valuable than a degree and obviously at a virtualisation event this mean a qualification from Vmware and/or Microsoft is what matters.  In the Vmware world you need to do the course and then do the exam.  In the Microsoft world you can just do the exam, but there’s a catch, those exams have gotten a lot harder, possibly harder than the equivalent VCP certification from VMware.  That’s not because Hyper-V is tougher to learn than Vmware especially if you have a windows server background already, rather it is to set the bar higher and build trust in the certification with employers

I also think hard exams are a good thing; they sort the wheat from the chaff, so you stand out from other candidates at interview time, and anyway what’s the point of doing them unless there is a true sense of achievement. Of course knowing what to get certified in is important, and given that virtualisation is relatively new and growing steadily in popularity in the UK I am going to stick my neck out and suggest that the first exam to go for would be 70-659: Windows Server 2008 R2, and Server Virtualization, especially if you have already got your head round windows server itself.  However as I said the exam is hard so I would suggest that you

a. try and setup some sort of demo environment (I have a 101 guide here plus there’s loads on TechNet)

b. get you head around the concepts by subscribing to the Microsoft Virtual Academy

Then you can decide if the exams are for you, and either persuade your boss to let you do them or failing that mug up from the numerous books out there buy or buy the e-learning kit from Microsoft that gives you everything you need, do the exams and join a more enlightened company once you’re qualified.

I am in the middle of SQL relay this week, but I just realised that you might like to have a copy of my Denali slide deck, which I have now posted to SkyDrive. You are welcome to have a look at it even if you couldn’t make one of the four venues I am presenting at this week, but there isn’t a lot of narrative included so you really need to be there.

Anyway the deck is on SkyDrive, please feel free to reuse but an acknowledgement of where you got it from would be a nice courtesy, and if you are considering looking at some of the cool stuff in SQL Server Denali please contact me to share your stories if you can.

Anyway tonight I am in Edinburgh, and tomorrow night at NEBytes, Newcastle University.