One of the confusing things about SQL Server is all the editions it comes in and one of the great things about SQL Server is er… all the editions it comes in.
Recent additions to this edition sprawl are the SQL Server DataCenter edition, the SQL Server Parallel data warehouse and SQL Azure. Why is this annoying and why is this good for you?
The annoying bit first…
Which edition should I use is a common question, and here I want to use the analogy of pizza. Go into any restaurant and they will have a list of the different pizzas on the menu based on their experience and feedback from their customers. But surely at the end of the day we are talking about cheese on toast here so how hard can it be? True but each customer is a little bit different and may have allergies and other dietary constraints and preferences. SQL Server works like this the editions are named for what they are used for web edition for the web, workgroup edition for workgroup , enterprise edition and so on. These have the mix of features in them that make them fit for purpose like compression, resource governor and all the BI stuff in enterprise edition. Of course you could adopt the “make your own pizza” approach that some other some vendors go for which is to give the basics at a given price and then charge for every single add on that you might want like compression clustering, support etc. but this can get quite difficult to manage across a suite of servers to say nothing of the higher costs this usually entails.
Which edition should I use is a common question, and here I want to use the analogy of pizza. Go into any restaurant and they will have a list of the different pizzas on the menu based on their experience and feedback from their customers. But surely at the end of the day we are talking about cheese on toast here so how hard can it be? True but each customer is a little bit different and may have allergies and other dietary constraints and preferences.
SQL Server works like this the editions are named for what they are used for web edition for the web, workgroup edition for workgroup , enterprise edition and so on. These have the mix of features in them that make them fit for purpose like compression, resource governor and all the BI stuff in enterprise edition.
Of course you could adopt the “make your own pizza” approach that some other some vendors go for which is to give the basics at a given price and then charge for every single add on that you might want like compression clustering, support etc. but this can get quite difficult to manage across a suite of servers to say nothing of the higher costs this usually entails.
The good stuff..
is the ability to run SQL server on anything from an embedded application on a device, to a small PC, all the way up to data centres and the cloud. This is a good thing for developers because the same application will just work on any of these without a rewrite, and dbas can use the same set of tools to manage any of these and replicate data across them if need be.
The point is that new editions have recently appeared in R2 to reflect new hardware and the cloud, but you don’t need to do anything radical to use them except install or subscribe, the rest you’ll be familiar with.
Although today is European safer internet day, I did my training yesterday to 650 secondary school pupils, as I have an event today in TVP as part of the day job.
There was stunned silence after they watched the excellent videos provided by CEOP (Child Online Protection Centre) , which are also available to teachers and parents on the ThinkUKnow website. CEOP are also providing both a Toolbar and Search add-ins for IE8 to make it easier to report abuse and raise awareness of the issues:
This is useful as not all sites commonly accessed by children have the report abuse icon that CEOP use, Facebook being an obvious example.
I heard on the way in that the Today team on radio 4, were stating that there were only 169 cases of grooming bought before the CPS, and were therefore downplaying the initiative a bit. So why do I bother with this?
OK rant over, back to preparation for today’s event on the Microsoft Application Platform in 2 hours and counting!
Although I have an Xbox, I still find flying and adventures are often better on the PC and I am not alone. I am also not alone in that I use 64 bit windows 7 at home for gaming according to this steam hardware survey…
What I am seeing here is that windows 7 is seen as a high performance platform in it’s 64 bit variant. This shouldn’t be a surprise because it’s big brother windows server 2008 R2 is only available as 64 bit. So where 64 bit has perhaps been less of a focus in previous windows versions (certainly for drivers, and third party apps) it is now seen as the future.
A good example of this is the NVidia drivers. My own experience on Vista running Flight Simulator X was that although everything worked the x86 version was noticeably slower than x64 and there were issues in other games (which might not be down to NVidia). However in Windows 7 everything works and in games like Crysis (which also comes in 64 bit form), I can crank up all the effects and details and enjoy all the eye candy it has to offer.
Flight simulator X running at 1920 x 1080p
The other thing that this highlights is that steam are able to run a successful business on the back of windows games (of whichever version). This is a bit at odds with the stuffy image of a PC user in a suit that some ad campaigns would have you believe. I am also pretty sure that the guys at Steam, Eidos, Electronic Arts etc. are also suit averse.
So if you’ve got 3Gb of RAM or more in your machine you might want to have a look at 64bit to make use of that with Office 2010 x64 or just to give your games a bit of a boost.
I hope my PowerPivot posts and videos are proving useful, and continuing this them I want to cover off installation.
PowerPivot for Excel is a free download for Excel 2010 and simply doesn’t need any edition of SQL Server behind it. It comes in 32 bit and 64 bit depending on which variant of Excel you have installed and you can get it the beta of this from the http://www.powerpivot.com site.
However to deploy that PowerPivot to SharePoint 2010, you’ll need to do quite a bit of work. Rather than rewrite these I will simply direct you to the set of draft setup guides on the PowerPivotGeek blog.
The basic setup is to..
I have used the first option, a single server setup to produce my PowerPivot videos and posts. However my demo rig is a bit more complicated in that I have three virtual machines (vms) on my shuttle (BINKY) which has 4 cores 8Gb RAM and 2 x NICs which are setup as follows:
It’s a miserable Friday out there so why not persuade the boss for a bit of R&D time and set this up this afternoon.
I like to make my demos myself and to try and make them as near real world as possible however I have two problems:
I use a shuttle PC (called BINKY) with a quad core CPU, 8gb RAM and 3 x physical disks in it. This has Windows Server 2008 R2 installed with just the Hyper-V role on it. From this I run all my virtual machines (VMs)…
I typically run three VM’s at the same time; the Domain Controller (DC), a Windows 7 client and a SQL/SharePoint server. This setup means that all my service accounts are domain accounts as are any users who access reports, sites etc.
I could put the Domain Controller (DC) role on the physical machine but this can be restrictive, and anyway is not ‘real world’. The key thing about this is the way I have setup networking and the virtual machine (VM) that is my DC ..
Two of these are controlled by the Hyper-V Virtual Network Manager, the Internal Virtual Network, and the External Virtual Network (both highlighted). Hyper-V can’t use wireless networks but a lot of my demos need internet access so I have bridged (the bottom entry above) the wireless network to the Internal Virtual Network to achieve this. The Local Area Connection is only set to have the Microsoft Virtual Network Switch Protocol enabled. Technically this is controlled by Hyper-V as well when you elect to use an external adapter and make it visible to the physical server..
Two of these are controlled by the Hyper-V Virtual Network Manager, the Internal Virtual Network, and the External Virtual Network (both highlighted).
Hyper-V can’t use wireless networks but a lot of my demos need internet access so I have bridged (the bottom entry above) the wireless network to the Internal Virtual Network to achieve this.
The Local Area Connection is only set to have the Microsoft Virtual Network Switch Protocol enabled. Technically this is controlled by Hyper-V as well when you elect to use an external adapter and make it visible to the physical server..
Remote desktop also enables me to share local drives to get data and apps in and out the VMs
With this setup all my VM’s have two adapters both of which have their ipv4 setting set to automatically detect, but can be resolved to the machine names as would happen in production.
My other tips for building demos with Hyper-V are:
Hopefully this is useful for anyone wanting to make a demo or sandbox environment but just be clear there is quite different best practice if you’re using Hyper-V in production and there are numerous posts on that including my favourite - Best practices for running SQL Server on Hyper-V
Although I am a huge fan of Microsoft BI (which is why I work for Microsoft not because I work for Microsoft) I realise that many of the factors behind a successful BI project are nothing to do with the technology. Of course the technology decisions can make a BI project more effective, more affordable and more agile, but that’s another story.
So when Mark Whitehorn asked me to do a session to his MSc BI course at Dundee University, we agreed on a title “Business Intelligence – it’s not about the technology”. My talk (whihc I gave today) was based on my own experiences using solutions from Business Objects, Cognos, Informatica etc. and of course Microsoft, but what I wanted to draw out was what to watch out for in the real world irrespective of the stack used.
After thinking about this for a while I eventually realised that the core of my talk would essentially prove the validity of Ralph Kimball’s readiness test for business intelligence. So for each of these tests I simply put in evidence of where there was a positive and negative example:
Strong Business Sponsorship from the senior partner in a major UK law firm got buy in from all parts of the business and meant that the project was completed on time (two days before I joined Microsoft) and on cost.
..however at one company I worked at the IT guys could see the need to update a reporting solution, but didn’t have the senior sponsorship needed, so a very tactical solution was all that was achieved, while some decisions seemed to have been made by the board during a round of golf. I wonder if that still happens there now?
Urgent Business Need. One successful project I remember was nothing more than a mainframe replacement. This did add extra ad hoc functionality, but a straight goal of like for like replacement and a very real need for a fixed go live date when the mainframe kept the scope and deadline fixed.
I have worked on a couple of white elephants especially when I first started and was too inexperienced and too junior to push back, the most obvious examples were often a board members pet project , so resources were made available but the project didn’t align with the business strategy (one one occasion there wasn’t one). Perhaps white elephants are rarer in these uncertain times.
Good collaboration between IT and the business has meant that I was able to train and lead a new BI team in a utility company. As a consultant this might be like eating your own children, but I wouldn’t be able to scale and do all their projects or handle the day changes and support that a good BI system needs if it is to keep up with the business. However they could always come back to me to assist in the bigger projects and to help them pitch ideas to their management.
On the other hand I was brought in by the business users in an attempt are made to go around what is seen by the (not me) as institutional inertia in s the IT departments. All went well until it was time to go into production where regular access was needed to data and production servers so the project hit the skids (to use a technical term).
Culture of Analysis. I have written several post before on flying on instruments and I worked in women’s fashion for a while where the merchandisers would make decisions to discount a line to ensure a balance between discounting too early and aggressively which reduces profit and too late where you have a warehouse full of unsold stock. Complex spreadsheets were used for this which were then replaced with a suite of exception reports.
Despite my best efforts to produce actionable insight at a brewery I worked at (tough work but somebody had to do it), the salesmen were all for using their experience to make decisions rather than believing what the reports were telling them. later the brewery were acquired and I guess the sales guys are know working elsewhere.
Feasibility Issues. I have come across which can block a project have included not being able to get at the data, and especially poor data quality around customer demographics. One other particular was in an insurance system where it impossible to resolve the data on screen to what was in the database because of the complexity of the application. This can happen in systems where the customer can design and alter the application to the extent that the schema changes.
This all sounds like doom and gloom, but I thoroughly enjoyed most of the projects I worked on. Of course the issues I have highlighted were frustrating but being aware of them before they became an issue meat this was kept to a minimum.
Having given my talk, Mark thanked me for pretty much summarising what he has been telling the students , and that’s interesting when there often seems to be so little consensus in IT these days, hence the post.
I just wish this course was around when I was starting out, so I didn’t have to learn it the hard way!!
My post Windows 7 x64 is fun might seem to echo quite a few comments on various forms and posts, but I do have a different view on how useful it is for business, based on about 18 months usage.
rather than regurgitate an endless feature list let me tell you what is really useful too me. To do that here is a typical day:
7am Arrive at the Microsoft campus because I hate traffic. I fire up my machine in seconds after slamming the lid shut the night before and instantly I am confronted with all the meetings I have come into the office for. hmm… the batteries looking iffy I wonder if George will let me order a new one? My machine has now seen the wireless network (you have to have a domain joined laptop to get on the wireless network) and the corporate spam floods in! 8am Check the session I am running today. I right click on PowerPoint select the top file and I have my deck. This is because I have PowerPoint pinned to the taskbar and the deck I will present is pinned as well so it’s always in my recent list. 10am presenting in Chicago (our main lecture theatre) plug in the VGA cable here logo key P and I am projecting ready for my session, let’s hope the demo gods are smiling! 12 noon Start writing my blog post around what I have just been speaking about. I use the side by side feature (logo key right or left arrow) to snap live writer to the right of my screen leaving Internet Explorer 8 on the other side so I can get some links to resources on TechNet. I also use that snipping tool to grab a few screens. 2pm push off home. When I get in and fire up my laptop I get a set of keys in the system tray telling me I need to enter my smart card credentials. This is DirectAccess and when I have signed in I have VPN functionality that is effortless and makes very good use of the limited network bandwidth I have at home. With my laptop in this state our IT team can check/enforce policies even while I am at home such as ensuring I have BitLocker encryption enabled. Actually I can’t get in if my machine isn’t up to date thanks to Network Access Protection. 3 pm I need to print my pass for TechEd Dubai and forgot to do this at the office. No problemo my home machines are running windows 7 and I have a HomeGroup which I can see by simply setting my network location to home and entering my secure passphrase to access it. Now I can print to my PC connected printer, pick up some music, but my wife on her PC can’t see my laptop as Home Groups don’t expose domain joined machines.
7am Arrive at the Microsoft campus because I hate traffic. I fire up my machine in seconds after slamming the lid shut the night before and instantly I am confronted with all the meetings I have come into the office for. hmm… the batteries looking iffy I wonder if George will let me order a new one?
My machine has now seen the wireless network (you have to have a domain joined laptop to get on the wireless network) and the corporate spam floods in!
8am Check the session I am running today. I right click on PowerPoint select the top file and I have my deck. This is because I have PowerPoint pinned to the taskbar and the deck I will present is pinned as well so it’s always in my recent list.
10am presenting in Chicago (our main lecture theatre) plug in the VGA cable here logo key P and I am projecting ready for my session, let’s hope the demo gods are smiling!
12 noon Start writing my blog post around what I have just been speaking about. I use the side by side feature (logo key right or left arrow) to snap live writer to the right of my screen leaving Internet Explorer 8 on the other side so I can get some links to resources on TechNet. I also use that snipping tool to grab a few screens.
2pm push off home. When I get in and fire up my laptop I get a set of keys in the system tray telling me I need to enter my smart card credentials. This is DirectAccess and when I have signed in I have VPN functionality that is effortless and makes very good use of the limited network bandwidth I have at home. With my laptop in this state our IT team can check/enforce policies even while I am at home such as ensuring I have BitLocker encryption enabled. Actually I can’t get in if my machine isn’t up to date thanks to Network Access Protection.
3 pm I need to print my pass for TechEd Dubai and forgot to do this at the office. No problemo my home machines are running windows 7 and I have a HomeGroup which I can see by simply setting my network location to home and entering my secure passphrase to access it.
Now I can print to my PC connected printer, pick up some music, but my wife on her PC can’t see my laptop as Home Groups don’t expose domain joined machines.
5pm Oh ***, I need to paste some links on my post that I’m working on. They’re in something I read a month or so ago, but exactly what I can’t remember. That’s simples - I just type in what I’m looking for from search in the start menu to get e-mails , documents in one list from my drive and designated sites such as the corp intranet and Bing.
I am not sure this could all be done on a heavily modified XP laptop but it would be slower for me and I would have to invest in third party tools for the encryption VPN etc. I absolutely know this because my wife has a shiny new laptop
which she uses to connect to her corporate network and it’s painfully slow.
I am also not sure if anyone who’s posting on windows 7 not being good for business is actually relying on it rather than just playing with it.
However I am very sure that I wouldn’t go back to using XP if you paid me whether that’s for the serious business of playing games or my hobby of Evangelist at Microsoft.
I am not sure if Mrs Fryer is trying to cash in on the life insurance or hasn’t quite got the hang of supply chain management. The thing is I keep seeing stuff in the fridge with “sell by October 2009”, and the odd bit of furry cheese.
Of course software doesn’t normally expire in the way that food does, but there are exceptions in the form of trial versions and beta products.
Windows 7 RC is probably the most downloaded beta to date and it is about to start to expire. This shouldn’t matter too much as it was only meant for evaluating the new operating system and helping Microsoft to test its reliability. However the reality is that many users still have it installed and this may well be on their only machine.
The way the RC will behave is as follows:
I mentioned upgrade above because if these people were unhappy with Windows 7 they would have reverted to what they had before. I suspect the continuing use of RC is partly a testament to how reliable it was (for a beta) and how good Windows 7 is. However the RC does have a few quirks in it, it is not supported nor will updates be released in future to secure it and should be consigned to the recycle bin as soon as possible, even if it was possible to go on using it forever.
So can I ask you to do two things:
Full details of this are on the KB article on Windows RC expiry
I often get asked about what I do in Microsoft, especially if my title of evangelist comes up in conversation. The latest addition to our team Sara Allison was also curious about what her colleagues get up to, so she interviewed me …
BTW Sara is our producer, so she’ll be doing all the cool creative web based stuff and has just started to blog on MSDN.
I have a confession to make; I have been doing a little bit of development! I wanted to check out one of the new features in Visual Studio 2010 / SQL Server 2008 R2, data tier application projects (confusingly shortened to DAC).
Essentially this is a simple lift and shift capability that allows a developer to deploy a database to production after which the DBA can manage it.
In VS2010 I can create a SQL Server Data Tier application ..
into which I can add database objects such as tables, views, stored procedures etc. from scratch. If I have a database already that I want to start developing then I can simply create a new project and then right click on it to import a data tier application (only from SQL Server 2008 R2 I’m afraid).
Now it’s in VS2010 I can compare it another version of the schema add objects, deploy it to another server e.g. the local copy on your machine complete with all the security logins etc.
You can also create a DAC from SQL Server Management Studio (SSMS) by selecting the database you want then Tasks –> Extract Data Tier Application to bring up this wizard. This creates a file of all of your settings into a file with a DACPAC extension which is actually a zip file full of xml files describing what you have extracted (which you can prove by changing the extension to zip & opening it).
BTW when I did this against a sample copy of AdventureWorks I got an error that ddl triggers aren’t supported in DAC, so watch for that.
The other interesting thing about DAC is that when it is deployed as such to a server it can also be monitored using the new Control Point feature in SQL Server 2008 R2, which I’ll leave for next time.
If you want to try any of this then there is the updated Developers Training Kit for SQL Server 2008 R2 which has examples and videos of this and a lot of the other new stuff for R2 (and the older one for SQL Server 2008 here).
OK enough of development back to the exciting world of infrastructure!
There have been tools to manage multiple instances of SQL server pretty much since it came out. If you don’t like the Microsoft tools or you have multiple database platforms in your business then there are quality tools around form the likes of Idera, Quest and RedGate amongst others.
However I want to concentrate on what is in the box specifically what’s new in SQL Server 2008 R2. The new feature is called Multi-Server Management on some of the slide decks you might have seen, but in the current CTP (November) of R2 it’s called the Utility Control Point (UCP) and that’s how I will refer to it.
The UCP performs two functions, it gives you very simple graphical representation of how your servers are performing and can also monitor the data tier applications I mentioned in my last post.
Here it is setup..
From this screen you can enroll new instances, and you can see the node for data tier applications which are automatically identified on a managed instance as they are deployed to it. Here’s the one I setup in my last post..
which is just idling on my demo rig so not too much to see but you get the idea.
Rather than clutter up this post with endless screen shots I have a separate article on getting started with multi server management, so if you want to try it out you can refer to that or watch the videos that are available when you first navigate to the utility control point (menu –> view –> utility explorer in SQL Server Management Studio)..
I do think this is nice and simple and will be useful for the casual dba who hasn’t invested in any third party tools, but this usefulness is limited to managing other instances of SQL Server 2008 R2.
If you want to try this, it’s all in the November CTP of SQL Server 2008 R2 which is the last public beta before it is released in May.
Whether you want your cloud solution from a search provider, a bookstore or a software house there is no denying that the next wave in the IT revolution is the cloud. Does that mean that IT professionals will go the way of the thatcher, millwright and blacksmith to be consigned to OU courses on social history. Is it some developer/management led conspiracy to remove what is seen as an obstacle to change in some organisations, or simply a way of cutting costs?
My assertion is this is not the case for several reasons.
So am I thinking about retraining to learn to be an electrician, or ramping up my guitar skills? No I am staying with SQL Server (including SQL Azure in the cloud), as I believe the demand for dba skills will continue long after I am in the ground.