Insufficient data from Andrew Fryer

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

June, 2008

  • A rough guide to Full Text Search in SQL Server 2008

    Full text search has been around in SQL Server since the 2000 edition and has gradually evolved in each subsequent release and it is included in SQL Server express so you don’t even have to buy it! In CTP6 (February) the latest revision appeared, The basic difference is that there is no longer a separate service and all of the indexes etc. are included in the database.  This has two important advantages:

    • Queries that combine a full text clause with a normal SQL where clause will be much quicker as the optimizer can work out a unified plan for the whole statement.
    • Backups will include the all of the full text stuff which gets rid of integrity issues ands makes recovery faster. 

    Indexing a big lump of unicode text in a column on a table requires little effort, but the real power of this feature is its ability to index various types of document stored in the database in varbinary(max) columns.  For this to work the engine has to understand the type of document that’s in there and to match this to a set of filters.  There are in built filters for office documents PDF’s etc.  However When SQL Server 2005 came out it came with a base set of these filters for the products around at the time such as Office 2003.  Since then Office 2007 has shipped and we have the new file types such as docx for word and xlsx for excel etc.    The good news is that you can add to the set of filters and the new office 2007 filters are here.  The not so good news is that if you’re working on SQL Server 2008 you’ll need to do the same thing.

    How does all this work?  Let’s have a look at the Adventure Works Production.document table as installed (note I am using SQL Server 2008):

    image

    In this table the document itself is in the [document] column and its type is in the aptly named [FileExtension] column. Because the table has not been setup for full text searching all of the options for it are greyed out in the table designer.  To fix this right click on the table and start the wizard:

    image

    and select design full text index.  The wizard will take you though what you need to do.  For this demo I:

    • Set the primary key to the primary key,
    • Selected only the [Document] column to be indexed , set the language to English, and set the type column to point to the [FileExtension column]. 
    • Didn’t use a stop word list.  A stop word list is  a list of words that are so common that they aren’t worth indexing as the resultant index will be huge. Examples of stop words in English are ‘it’, ‘and’, ‘the’ and especially ‘so’ if you work at Microsoft!
    • Created a new full text catalog ‘demo’.
    • Set the change tracking to automatic i.e. any change to the [document] will automatically update the full text index. which maybe something you don’t want to do in production as this can slow down the server for large indexes. 

    So now the index is ready to use and in my next post I’ll show you how to use it.  In the meantime there are lots of resources on this  including:

  • SQL Server 2008 RC0

    image

    I was going to post about how to search full text in SQL server 2008, but hold the phone SQL Server 2008 is now at RC0 on TechNet plus subscriptions here.  There are 2 reasons for me mentioning this:

    1. It’s not on connect yet so this is one reason for getting a TechNet direct subscription and as per my post here I can get you 30% off: just go to the TechNet subscription page or call 0800 281 221 and quote UKITPRO4.
    2. Full Text searching is not a good story in CTP6, because the service account (FDH$MSSQLSERVER) it uses is not setup properly during installation e.g. it’s not got logging as a service, and has password must change at next login and even then I couldn’t get it working before I got the word about RC0 (see this thread on Connect for more information on the issue).

    You might think that as a Microsoft automaton (the neighbours call me Android) that I get this stuff weeks in advance, and indeed I could get interim builds but I don’t see the point in being to far ahead as I want to have the same experience (good or bad) as you do.

    What to do while I wait for it to download….hmm

    Technorati Tags: ,
  • SQL Server 2008 RC0 feature pack

    The sharp eyed amongst you will have noticed that there are a few things missing in RC0 that were in CTP6 and I was a bit annoyed that one of my favorites, the report designer preview had disappeared.

    Don’t panic it’s tucked away in the SQL Server 2008 RC0 feature pack along with lots of other goodies including:

    Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider

    Note: Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider requires Microsoft Core XML Services (MSXML) 6.0, also available on this page.
    Audience(s): Customer, Partner, Developer


    Microsoft SQL Server 2005 Backward Compatibility Components

    The SQL Server Backward Compatibility package includes the latest versions of Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL Virtual Device Interface (SQLVDI). These versions have been updated for compatibility with both SQL Server 2005 and SQL Server 2008 and include all fixes shipped through SQL Server 2000 SP4 and SQL Server 2005 SP2.
    Audience(s): Customer, Partner, Developer


    Microsoft SQL Server 2008 Command Line Utilities

    The SQLCMD utility allows users to connect, send Transact-SQL batches, and output rowset information from SQL Server 7.0, SQL Server 2000, SQL Server 2005, and SQL Server 2008 instances. The bcp utility bulk copies data between an instance of Microsoft SQL Server 2008 and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.
    Note: This component also requires both Windows Installer 4.5 and Microsoft SQL Server Native Client (which is another component available from this page).
    Audience(s): Customer, Partner, Developer


    SQL Server Compact 3.5 SP1

    SQL Server Compact is a free, easy-to-use embedded database engine that lets developers build robust Windows Desktop and mobile applications that run on all Windows platforms including Windows XP, Vista, Pocket PC, and Smartphone. Run the Microsoft Windows installer file (MSI) to install the product. The single setup program will install the provider and tools on x86 and x64 computers. The package includes product updates in the form of an integrated Service Pack 1. Read the installation guide and Readme for more information.
    Audience(s): Customer, Partner, Developer


    Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007

    Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007 enables you to take advantage of SQL Server 2008 predictive analytics in Office Excel 2007 and Office Visio 2007. The download includes the following components:

    • Table Analysis Tools for Excel: This add-in provides easy-to-use tools that leverage SQL Server 2008 data mining features to perform powerful analytics on your spreadsheet data. Two new tools have been added for this release: Prediction Calculator and Shopping Basket Analysis.
    • Data Mining Client for Excel: This add-in enables you to go through the full data mining model development lifecycle within Excel 2007 using your spreadsheet data or external data accessible through your SQL Server 2008 Analysis Services instance. This release adds support for new SQL Server 2008 data mining features including holdout and cross-validation, a new Document Model wizard, and improvements to existing wizards.
    • Data Mining Templates for Visio: This add-in enables you to render and share your mining models as annotatable Visio 2007 drawings.
      Audience(s): Customer, Partner, Developer
        X86 Package (SQLServer2008_DMAddin.msi) - 18100 KB
        Readme (DMAddins_SS08_Office2007_Readme.htm) - 24 KB

    Microsoft Core XML Services (MSXML) 6.0

      Microsoft Core XML Services (MSXML) 6.0 is the latest version of the native XML processing stack. MSXML 6.0 provides standards-conformant implementations of XML 1.0, XML Schema (XSD) 1.0, XPath 1.0, and XSLT 1.0. In addition, it offers 64-bit support, increased security for working with untrusted XML data, and improved reliability over previous versions of MSXML.

    Microsoft SLQ Server 2008 Management Objects

    The SQL Server Management Objects (SMO) is a .NET Framework object model that enables software developers to create client-side applications to manage and administer SQL Server objects and services. This object model will work with SQL Server 2000, SQL Server 2005 and SQL Server 2008.
    Note: Microsoft SQL Server 2008 Management Objects Collection requires Microsoft Core XML Services (MSXML) 6.0 and Microsoft SQL Server Native Client, also available on this page.


    Microsoft OLEDB Provider for DB2

        Package (DB2OLEDB.exe) - 44607 KB


    Microsoft SQL Server 2008 Native Client

      Microsoft SQL Server 2008 Native Client (SQL Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2000, 2005, or 2008. SQL Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2008 features. This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2008 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API.
      Audience(s): Customer, Partner, Developer


    Microsoft SQL Server 2008 Policies

    Microsoft SQL Server 2008 Policies are examples of how you can take advantage of Policy Based Management. These policies will help you follow some of the SQL Server best practices and avoid common pitfalls. For more information, please see Administering Servers by Using Policy Based Management in SQL Server 2008 Books Online.
    Note: This component also requires Windows Installer 4.5.
    Package (SQLServerBestPracticesPolicies.msi) -1343 KB


    Microsoft SQL Server 2008 Report Builder 2.0

      Microsoft SQL Server 2008 Report Builder 2.0 provides an intuitive report authoring environment for business and power users. Report Builder 2.0 supports the full capabilities of the Report Definition Language (RDL) including tablix, new data visualizations, and richly formatted text features of SQL Server 2008 Reporting Services. The download provides a stand-alone installer for Report Builder 2.0.
      Note: This component also requires .NET Framework 3.5.
      Package (ReportBuilder.msi) -18640 KB


    Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies

      Microsoft SQL Server 2008 Reporting Services Add-in for SharePoint Technologies allows you to take advantage of SQL Server 2008 report processing and management capabilities in SharePoint integrated mode. This version also includes data-driven subscriptions. The download provides a Report Viewer web part, web application pages, and support for using standard Windows SharePoint Services or Microsoft Office SharePoint Services.
      Download site


    Microsoft SQL Server System CLR Types

      The SQL Server System CLR Types package contains the components implementing the new geometry, geography, and hierarchyid types in SQL Server 2008. This component can be installed separately from the server to allow client applications to use these types outside of the server.
      Note: This component also requires Windows Installer 4.5.
      Audience(s): Customer, Partner, Developer


    Microsoft SQL Server 2008 Upgrade Advisor

      Microsoft SQL Server 2008 Upgrade Advisor analyzes instances of SQL Server 2000 and SQL Server 2005 in preparation for upgrading to SQL Server 2008. Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.
      Note: This component also requires Windows Installer 4.5.
      Audience(s): Customer, Partner, Developer

    I have to say I am puzzled as to why some of this has moved out of the install, although some of it is included in both, for example the upgrade advisor.

  • SQL Bits Logo design

    I have heard it said by many developers that DBAs are all artists, so please can you direct your creative talents to coming up with a new logo for the SQL Bits events. Simon Sabin, designed the last one but it needs to be changed for reasons I won’t bore you with:

    Note: Don’t be a square come to the cubed event like it says at Hatfield 13th September.

    Please send your logo ideas to Simon, and I will organise the prize, probably something with the new SQL Server 2008 new logo on:

    SQL08_h_rgb

    I have to say I like the new triangular thing for SQL Server 2008 BUT it’s shared with other products including BizTalk so I am pretty upset that the product marketing guys couldn’t justify something unique for SQL Server.  I quite liked the SQL Cans on the jumpstart t-shirts:

    IMG_2711

    They look sort of database related and are about the coolest Microsoft design I have seen on a shirt.

    Technorati Tags: ,
  • Migrating a virtual machine from Virtual PC to Hyper-V

    Now that hyper-v is released I though it would be good to go through a little glitch I found moving a Virtual PC vm (sometimes known as a VPC) to a hyper-v vm.

    As I have mentioned before my trusty heavyweight laptop dual boots to Vista for day to day work, and Windows Server 2008 for demonstrations and so on.  I don’t have SQL Server 208 installed natively on either OS and won’t until SQL Server 2008 is released to the public.   Because of this all my stuff is in virtual machines (vm).  I have Virtual PC installed in Vista while Windows Server 2008 has the Hyper-V role installed as I have a 64-bit laptop which specifically supports virtualization (set in the BIOS).

    I needed to build a new vm with SQL Server 2008 rc0 and Visual Studio 2008 running on Windows Server 2008 and I did all this in Virtual PC for two reasons:

    • I didn’t care that it would only be a 32bit environment (only hyper-V support 64-bit guest vm’s).
    • I wanted to do admin stuff while it was building which I don’t have setup on the Windows Server partition for reasons I won’t bore you with.

    The problem came when I wanted to migrate it to being a hyper-V machine, in that it was running like a snail and so I have now written these steps to overcome the issue: 

    Step 1. Like any good DBA make a backup by copying the vm so you have a fallback if things go wrong.

    Step 2. Uninstall the Virtual PC virtual machine additions in the vm while running it in Virtual PC. Virtual machine additions enable the mouse to move smoothly from the host to the guest vm and allows you to share folders and improves performance by using some of the Hyper_V technology (if your hardware can support this).  Hyper-V has similar integration components whihc are entirely different and use different key strokes to Virtual PC e.g. login in virtual PC is <Right- ALT> <Delete> while in Hyper-V it is <CTRL><Alt><End>.

    Step 3. Fire up Windows Server 2008 and go into the hyper-V role. If you haven’t got Hyper-V setup then please go here for a step by step guide.

    Step 4 Create  a new VM  and point to the .vhd (virtual hard disk) of the machine to be migrated.

    step 5. fire it up in Hyper-V and check all is well in device manager as per this screen grab…

    image

    If you have a warning triangle here, then the performance will be atrocious i.e. similar to virtual PC without its virtual machine additions installed.  To fix the problem launch msconfig and select advanced options from the boot tab and turn on detect HAL (Hardware Abstraction Layer)…

     image

    Once that is fixed, the performance is very nearly as good as a physical machine, but bear in mind that you should still do the same sort of disk layouts as you had with virtual PC i.e. put the vhd on a different physical disk than the physical OS if possible, even if this is a slower usb drive.

  • Using Full Text Searching SQL Server 2008

    I have now got SQL Server 2008 RC0 running in my Hyper-V 64-bit virtual machine, complete with the sample adventure works database from connect. Now it’s time to have a look at querying full text indexes. One of the common ways of doing this is to use the contains function as part of a where clause and combine this with a filter on the structured e.g

    SELECT    
        PM.Name AS ModelName,
        PC.Name AS Culture,
        P.Name AS ProductName,
        PD.Description,
        P.ProductNumber
    FROM        
        Production.ProductModel PM
    INNER JOIN  
        Production.ProductModelProductDescriptionCulture PDC
        ON PM.ProductModelID = PDC.ProductModelID
    INNER JOIN
        Production.ProductDescription PD
        ON PDC.ProductDescriptionID = PD.ProductDescriptionID
    INNER JOIN
        Production.Product P
        ON PM.ProductModelID = P.ProductModelID
    INNER JOIN
        Production.Culture PC
        ON PDC.CultureID = PC.CultureID
    WHERE    
        CONTAINS(PD.Description, 'Performance')
        AND (P.ProductNumber LIKE N'FR%')

    One thing to note about this query is that it will be much faster in SQL Server 2008 as it will yield a single execution plan:

    image

    with a table valued function for the full text match. BTW the issues I was having getting Full text to work in CTP6 have gone away and all of this just worked after install.

    The contains function can support all sorts of searching operators for proximity matching, applying different weight to different search terms as well as the ability to search from a list of columns in the same table, provided they are in the same language i.e. they have been indexed with the same language.  Check Books On line for more information here

    The same syntax is also good if the column you’re searching is actually a document in Varbinary(MAX), and the new Filestream data type.  So if I index the documents table (as I did on Thursday ) then I can run a query like this…

    SELECT    
    FileName,
    [Document]
    FROM        
        Production.[Document]
    WHERE
        CONTAINS(Production.[Document].[Document], 'red')

    and get back this

    image

    of course you will want to write some code to stream that document back to the user in a more readable form, but the point is that the search has got inside the document and realised it contains the word ‘red’.

    If you want to search across multiple tables for a term then your query will look more  like this (BTW I setup the product table for full text searching before running this):

    DECLARE @SearchTerm Nvarchar(50) = N'road'
    SELECT    
        PM.Name AS ModelName,
        PC.Name AS Culture,
        P.Name AS ProductName,
        PD.Description,
        P.ProductNumber
    FROM        
        Production.ProductModel PM
    INNER JOIN  
        Production.ProductModelProductDescriptionCulture PDC
        ON PM.ProductModelID = PDC.ProductModelID
    INNER JOIN
        Production.ProductDescription PD
        ON PDC.ProductDescriptionID = PD.ProductDescriptionID
    INNER JOIN
        Production.Product P
        ON PM.ProductModelID = P.ProductModelID
    INNER JOIN
        Production.Culture PC
        ON PDC.CultureID = PC.CultureID
    WHERE    
        CONTAINS(PD.Description , @SearchTerm )
    OR
        CONTAINS((P.Name, P.ProductLine) , @SearchTerm )

    I have highlighted the multiple column clause here in orange , as  I included both of these columns when I setup full text indexing ion the Product table.

    The query plan for this statement looks like this:

    image

    This is not such a good performance story and books on line recommends here that you don’t have multiple contains statements because of this.  In this example we are stuffed as we are full text searching across multiple tables.  If you need to do alot of full text searching on a large index , then design your database with full text searching in mind i.e. try and keep the number of tables that you will want to combine in a text search to a minimum. If you think about this it’s no different from using T-SQL to search lots of columns for the same thing, and so this limitation is not as much of a constraint as it initially appears.

    So contains is no substitute for good database design it’s just another tool, to quote Abraham Maslow…

    “If the only tool you have is a hammer, then you tend to see every problem as a nail”.

  • Is your Journey Really Necessary

    If there was a war on perhaps the government would bring back posters like this one from World War II asking the public not use public transport unless it was absolutely essential.  Petrol was rationed, so the price was irrelevant.

    Changing the subject for a minute think about these two scenarios :

            • take one healthy frog and drop it into a bowl of boiling water: result frog hops it pronto.
            • take the same now nervous frog and put into a bowl of cold water.  now over a period of thirty minutes gently bring to the boil: result poached frog.

    (no frogs were harmed during this thought experiment)

    So as fuel, food and water (making and supplying fresh water is very energy intensive) gradually rise, we just grumble a bit and hope the government will sort it out for us.  Contrast this with “Any society is only three meals away from revolution” (Rimmer from Red Dwarf?) and you see why I mentioned frogs. 

    This is the problem with all the hype about the environment , it’s difficult to see how your behavior affect the problem, and although I agree with Steve and James about the need for sustainable societies, I am not sure that anything will change much unless there is a shorter term benefit in all of this at a personal, business and political level.   

    So you realise it might be much better to work from home one or two days a week.  The environment benefits, someone else can use your seat on the train or your parking space, but is your user community happy about that, and does your manager trust you to do the right thing when your not under their nose?

    Better yet if you can leverage some technology to enable your users to work flexibly like we do at Microsoft. Is that investment OK with the board, trustees etc and what’s in it for them?  You probably wouldn’t get far saying it would save a few tons of carbon, but you could reasonably argue that the benefit is a better motivated workforce, and a more attractive place to work (which helps with recruitment and retention) and reduce the need for as much office space into the bargain.

    So my advice is to try a side on approach and like any good IT idea you have imagine you are asking the questions in Dragon’s Den!

  • Chat to Us about SQL Server

    Why do so many SQL Server people make the effort to come to evening and weekend events?  These people can all read so surely they can find what they need on-line.  Many already know a lot about the product and have a lot of experience behind them.  I believe that part of this is to debate and discuss ideas, and that the more experienced DBA take genuine pleasure in helping others to get into the product.  That’s certainly what I try and do, but I can’t be everywhere and neither can the other like minded individuals in Microsoft so we have come up with a cunning plan…. 

    We’ve put an instant messenger gadget on the SQL Server site.  It’s all anonymous at your end so for all I know I could be debating the merits of change data capture with Larry Ellison.

    image

    I would add that this is not intended as a replacement to the excellent service our support and premier filed engineers provide.  It’s more about pointing you in the right direction and discussing options or clarifying such arcane subjects as editions, and licensing.

    The first question I had to answer from an on-line discussion using this was “when is SQL Server 2008 going to be released?” 

    I actually have no idea, but no one really believes that. But suppose it was all a big secret and the date is 10:24 GMT on August 31st, how does that help you? Will there be queues of DBAs lining up at the Microsoft Campus hoping to get an early copy – it would be nice but I don’t think it’s likely. 

    The reality is that if it was out today, the next question I would get is “when is SP1 coming out?” 

  • Windows Server 2008 – Branch Office Deployment

    In the final part of my interview with Alun Rodgers of Risual, we discuss the rollout out of Windows Server 2008 to branches of a well known fashion house.  You won’t get any tips on dress sense watching this, but you will understand why such  features as the Read Only Domain Controller and BitLocker are needed to support remote offices.

    image

  • Analysis Services 2000 to 2008

    Analysis Services 2000 was  my life for several years and like a good friend you tried to appreciate the good things and try and work around what is not so good.  I enjoyed how seductive and easy it was to build a cube, I hated the fact that you could leave the designer open go home and the cube wouldn’t build because of that, but you told yourself it was your fault not the product teams. 

    Then out comes SQL Server 2005 and it jars. The ease of use seems to have gone, where are my virtual cubes and dimensions and what on earth is all this new stuff – the measure groups, the attribute relationships to name a few?

    I was lucky enough to get trained up by Reed Jacobsen and he was unlucky enough to have broken quite a few bones and was strapped up while giving the course.  Anyway I got the idea behind the new Analysis Services, I understood why the data source views were important (to abstract the data from the source), and I saw how Microsoft had embraced Ralph Kimball’s bus architecture when I saw the dimension usage grid in the Business Intelligence Design Studio.  A good way to describe both versions is that in Analysis Services 2000 everything is hierarchy based while in 2005/8 it’s attribute based.

    This complete overhaul of the Analysis Services came at a price and that is backward compatibility. Moving from Analysis Services 2000 to 2005 generally involves redeveloping the cube in all but the simplest cases.  However I don’t really see this as a big deal as rebuilding a cube is only a small part of a BI project. If most of the design stays the same it can be fairly simple mechanical exercise,  The biggest part being testing and confirming the numbers are OK in the new version.

    The next version of Analysis Services in SQL Server 2008 is just around the corner, and it’s time for more change, but not as much as last time. I am reminded on the transition of OLAP Services in SQL Server 7 to Analysis Services 2000 in that the changes are subtle but significant. The 20005 cubes will migrate to 2008 without having to be reprocessed, but there changes and I have listed a few of my favourites here:

    • The new graphical attribute designer lets you see your design more clearly
    • Best practice warnings underline anything which may affect the performance of your cube, in addition to red underlining showing any errors
    • The block computation can radically speed up queries across sparse data
    • Dynamic grouping lets you create a list of top ten selling products that reflects the latest data.
    • Backup performance is now linear i.e. a cube that doubles in size will take twice the time to back up rather than three or four times longer as is currently the case.

    So you might then reasonably consider moving straight to SQL Server 2008, from 2000 as there will be no additional work involved and no additional gotcha’s. 

  • Dr Michael Rys – meet the expert.

     

    For those that don’t know him Michael Rys is the principal program manager lead for SQL Server’s Beyond Relational Data team and represents Microsoft on the W3C xml query working group.  While he’s over in the UK next week he’s volunteered to speak at a special SQL Server community event on 30th June, which is very short notice so my apologies!

    The details of the event are on the UK SQL Server Community site (on the events tab) and you’ll need to register as places will be limited:

    Agenda

    6:30pm - 6.45pm - Introduction, news and gossip.
    Whats going on in the world,

    6:45 - 9pm An Introduction to the New Spatial Data Support in SQL Server 2008 by Michael Rys

    Spatial data is fundamental, modeling the locations and shapes of objects in the world to enable visual analysis of data. This session will be an introduction to working with spatial data in SQL Server. We will describe the different types of spatial data, give business examples of the use of spatial data, and provide an overview and demonstration of the new spatial features in SQL Server 2008 such as the spatial types, their methods, how to visualize and how to index it.

    Location

    Conchango Offices
    36 Southwark Bridge Road
    London

    Map image

    I look forward to seeing you there.

  • Windows Server 2008 – Terminal Services

    In the second part of my interview with Alun Rodgers of Risual, we discuss what’s new in Terminal Services in Windows Server 2008, and how he has been helping his customers to implement it.  One use I hadn’t come across was to to provide access to a secure system as part of a normal users desktop, but Alun can explain it better than I can so take it away Mr Rodgers…

    image

  • SQL Server & Open Source Interoperability

    You needed to understand two acronyms In the Microsoft vs Open Source debate until recently:

    • LAMP:
      • Linux
      • Apache
      • MySQL
      • PHP
    • WISA
      • Windows
      • IIS
      • SQL Server
      • ASP.net

    But the world has moved on and Microsoft like all software vendors recognises the value of open source and so there is now lots of in between choices giving rise to some new acronyms  (although these do all rely on Windows as SQL Server won’t run on anything else):

    • WASP
    • WISP

    What this means is that SQL Server can work with PHP and sites can be hosted on Apache.  Of course if you really hate using SQL Server even when it’s free (SQL Server Express) then you can still have the technically non-open source Sun MySQL and use:

    • WIMA
    • WIMP

    Is this Microsoft being nice, avoiding further battles anti-trust battles on either side of the atlantic, or another attempt at world domination of the IT industry?  My view is that everybody likes to have a choice (so why is there only one monopolies commission then?), and this is particularly true of programming languages.  PHP is very appealing, and has wide spread support so it would be foolish to ignore it. 

    The great thing about open source IMO is the collaboration and code sharing that goes on.  I would argue that there is loads of this in the SQL Community.  You only have to trawl the net for SQL blogs and forums to see the rich variety of people involved.  Of course these people don’t write the actual product, but then that’s a very small group of developers compared to the people who use it.  What the SQL Server community does is develop best practice and write miles of scripts and then share their experience off and on-line.

  • Analysis Services debugging& tuning

    I keep recommending IT Professionals to check out codeplex for the various samples that are on there to make life easier, but I haven’t been on there myself for a couple of weeks (doh!).  This site has largely taken the place of the resource kits that used to come out for use with Windows, SQL Server etc. Anyway the list of good stuff on there for analysis services is growing steadily, with a strong emphasis on management, testing and performance tuning:

    • Compress M2M C# Application
    • Methodology for Monitoring Analysis Services (V2)
    • Script for Creating a Processing Log File
    • PowerShell Scripts for Querying Analysis Services 2008 DMVs
    • AS Load Sim, including Load Testing Best Practices document
    • AS Query Generator
    • Load Test Reports
    • RSS Style Sheet
    • Activity Viewer
    • Aggregation Manager
    • AS Trace
    • Analysis Services Upgrade Verification Tool

    As you can see from the surprisingly short licensing tab, this is all to be used 'as is' like the resource kits.

    I am surprised at how few downloads there have been of these tools, I can sort of understand this for the new PowerShell utility as you need to be testing SQLK Server 2008 for that, but surely the ASTrace utility should be in use anywhere you are looking to improve the performance of large cubes.

    So look good in front of your peers and try this stuff, and if you want to look really good post something up there for the rest of the community to use.

  • Microsoft SQL Server Community Utilities

    As well as the independent SQL communities, there is also a Microsoft run one, the Microsoft SQL Server Community Worldwide ( You can tell it’s the Microsoft one by the catchy title).  They do tend to assume worldwide is the United States wide or even the Seattle area so you can drop in to Redmond for SQL school.  However don’t let that put you off, there is some really good stuff there including a list of utility scripts here  as long as your arm for probing the dark corners of your installations:

    One thing, the download button is hidden away when you want to get one of these scripts…

    image

  • Running SQL Server on Windows Server 2008

    I pulled down SQL Server 2008 RC0 over the weekend and I thought it would be good to get this running on Windows Server 2008.  There doesn’t seem to be a lot of resources around on this but it’s not that hard, in fact you pretty much install Windows Server 2008 and as soon as it’s done put SQL Server on top.

    Don’t take my word for it, I talked to Alistair Joy  a DBA at Michael Paige Recruitment about his experience of Windows Server 2008 and SQL Server which you can watch here:

    image

    Thanks to Alistair for taking the time out to be on video, and to Michael Paige (the third largest recruitment company on the planet) for letting me share this with you.

    BTW Sorry about the sound quality on some of this, next time I will remember to take a separate podcast device with me.

    I need to finish my article on full text search and I want to use RC0 for that, but I though it would be good to show you that installation so that will be my next post.

  • Installing SQL Server 2008 rc0

    Installing SQL Server 2008 RC0 on windows server 2008 is very straightforward, except that  I want to show SQL Server 2008 in its best light for a couple of events next week, and, so I am doing my first install on a clean 64 bit Hyper-V virtual machine with windows server 2008 already installed. 

    My laptop (4gb dual core) dual boots Vista 64 bit / windows server 2008 with the Hyper-V rc1 role installed.  It’s worth noting at this point that if you do want to use Hyper-V you will need to do stuff in the BIOS before you start, and as you can see below modern machines have virtualization settings in the BIOS and Hyper-V won’t work on older machines without these:

    image_thumb[18]

    Anyway having got past that I fired up the setup on the RC0 iso file, by going to the hyper-V media option and loading the iso file. Step 1 is to install the  .Net framework 3.5 and reboot to run the setup again, to actually start installing SQL Server 2008:

    image_thumb[26]

    As you can tell from the catchy title on the iso,  this install has all editions for all platforms from x86 express through to ia64 enterprise.  You can get keys for this from the TechNet subscription site here, if not then you have 180 days to evaluate.

    The install is looking much more mature, for example upgrade advisor is directly installable form here. The options on the right hand side reveal tools to warn you about installation showstoppers and also allow you to change the edition of SQL Server you’re using e.g. standard to enterprise.  I am going for x64 enterprise. I liked the subtle checking here:

    image_thumb[33] 

    .. for issues that have caught all of us out in the past, such as the domain controller check and looking for ctp6. However, I have seen some issues around upgrading from CTP6, so if you do spot anything please, let me know or register the fault on connect:

    The next step is to select the options you want:

     image_thumb[42]

    Then the server configuration needs to be defined, including the usual suspects: what accounts to run the service under, mixed mode sa password, oh and don’t forget to add yourself as a sysadmin for the database and analysis services:

    image

    When your happy let it go and put the kettle on:

    image

    If all is well you will be ready to go in about twenty minutes. Now, finally I can get back to full text search where I was on Friday afternoon!

  • What do Evangelists do at the Weekend?

    This weekend the Microsoft evangelists were out in force at the BBC Mashed, a hack event at Alexandra Palace.  The delegates have 24 hours to make something cool with sponsors stuff, and those sponsors also included the Lonely Planet, The BBC, O’Reilly publishers, Channel 4, The Guardian, and FireEagle (mobile tracking).

    IMG_2614

    We were there to help use our stuff, Popfly, Multi-Map, Silverlight,  etc. so really off topic for me, so I was more into helping out on the hardware side including this odd looking dual core blimp which was our eye in the sky (we needed both balloons to lift the camera and a couple of 9v batteries).

    image

    We weren’t there to win and Paul Foster our judge on the panel quite rightly called out a cool app from the North Enders team that took the subtitles from a BBC programme (in this case East Enders), translated them into a language of your choice and then read that out more or less in sync with the original video, although sounding a bit like Stephen Hawking’s speech synthesiser.

    Am I at work when I do this? technically yes as I was there representing the firm, but would I have gone anyway, probably yes, because evangelism is all about helping people do stuff, with a little nit of fun built into the equation.

  • Windows Server 2008 Hyper-V – is it any good?

    I was presenting SQL server 2008 at a NextGen community event and there was a Windows Server 2008 given by Alun Rodgers of Risual (a gold partner specialising in server infrastructure and unified comms).  His session was so well received I thought it would be good to get him to share his experiences in a series of short blogcasts.

    In the first of these Alun talks about what he has been doing with Hyper-V, both internally and for his clients.

    image

  • Weather in Business Intelligence

    We are obsessed by the weather in this country, but I can’t say I have ever actually used it in a business intelligence solution.  I know that Iceland (the frozen food people not the country) have a model that identifies an ideal BBQ day i.e. not too hot or cold, no rain, low winds and so on, preceded by several days of similar conditions.  Other types of retailers also quote weather to account for abnormal sales of clothes etc.

    However unless you work for the Met Office you are unlikely to have the raw data you need already in your databases.  I am sure the Met Office would be delighted to share their data with you but like the Ordnance survey this can be pretty expensive, considering we have already paid for this through taxes.  So here’s how to get the data for you to try for free.

    Fortunately the US Government have taken a liberal interpretation of “the land of the free”  and so you can look up the current weather for pretty much any airport in the world in hourly intervals back for 24 hours. There is no web service for this but there is an ftp site from the American National weather service , so you could easily write an SSIS task to pull in the data you need, airport by airport. 

    You will then need to map the geography you have (Say your stores) to their nearest airport in the list.  I might suggest that this would be a good place to get into the new geo-spatial stuff in SQL Server 2008.  If you have the latitude/longitude of your locations you can use min([mylocation].STDistance([airportlocation]) for each location to get the nearest airport.  Of course you’ll need airport locations and you can get the entire worlds airport locations (for free!) here

    Of course there is going to be a bit of work to get this going, and it might initially be hard to justify that until you have done some analysis. The simplest and cheapest approach to doing the actual analysis is going to be the data mining add-ins for excel that you can get here for SQL Server 2005 and here for SQL Server 2008.  If you’re not sure about data mining generally please check these blogcasts on TechNet spotlight.

    So now you can see how anything from school attendance, sales of sun cream , doctor’s appointments etc. might be affected by the weather.