> the sum of its parts

FullText Search fails to start on Windows 2008 Cluster

2009-01-07T09:36:00Z

I’d recently built a two-node failover cluster for a customer using Windows Server 2008 and it’s working really well. The customer wanted to extend the cluster by adding a third node and I came across a problem with the Full Text Search service following the SQL Server installation.

The new node was introduced to the failover cluster – no problem. I’d re-run SQL Server setup.exe on the new node for each of the three instances, then run Service Pack 2, plus CU7 to synchronise the SQL binaries on the new node with the existing nodes. I’d adjusted the Possible Owners and Preferred Owners to reflect the intended failover actions. Next I wanted to test failover of each instance to the new node to validate SQL Server started and there were no problems.

Everything worked fine, except FullText Search – which wouldn’t start, the following appears in the Application Log:

The SQL Server FullText Search (SQLInst2) service depends the following service: NTLMSSP. This service might not be installed.

There is a mention of this problem in the following KB article, although the resolution is to install SP2 for SQL 2005 – which had already succeeded.

List of known issues when you install SQL Server 2005 on Windows Server 2008

After some further research and testing - I changed the following registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\msftesql$SQLInst2\DependOnService

The DependOnService showed two values RPCSS, NTMLSSP on the new node, but the same key only showed RPCSS on the existing 2 nodes (where the instances failover without problem). Removing NTLMLSSP from this registry key for all three FullText Search instances on the new node and restarting the server resolved the problem – and the instances now failover.

Regards,

Justin Langford

Coeo - SQL Server Consultants | SQL Server Remote DBA

SQL Server 2008 - Cumulative Update 1 released

2008-09-23T08:11:00Z

The SQL Server Sustained engineering group continue the Incremental Service Model for SQL Server 2008, and begin with the first Cumulative Update for SQL Server 2008! Relased today, this CU contains 80 fixes - including fixes to intellisense, replication and clustering (among others).

Further details of SQL Server 2008 Cumulative Update 1.

The incremental service model provides a method for the engineering team responsible for ongoing maintenance of the SQL Server code based, with a planned delivery route whereby customers receive frequent, small (incremental) updates (CUs are released every 2 months). This was approach was very successful with SQL Server 2005 and enables the sustained engineering to be responsive - providing frequent, quality releases – great work!

Further details of the Incremental Service Model.

Regards,

Justin Langford.

Coeo - SQL Server Consultants | Remote DBA | Dedicated Database Engineering

Zero downtime database upgrade - SQL Bits III

2008-09-18T20:09:00Z

At SQL Bits III - I presented on my experience with Zero Downtime Database Upgrades. If you weren't at SQLBits or didn't manage to make it along to the session, I've included a précis of the options below and the slides are available for download. The session focussed on the methods and processes to support an upgrade project while minimising downtime.

Upgrading SQL Server 2000 (downtime <2 minutes)

It's possible to upgrade SQL Server 2000 to either SQL Server 2005 or 2008 while minimising downtime using log shipping. This method involves manual log shipping to synchronise the new (target) server as close as possible with current production. At time of cutover, the old database is set to READ ONLY, take a final log backup and recover the database on the target server with this log. Downtime in this scenario is the time between setting the database to read only and enabling the database for read/ write again on the new server - typically a couple of minutes.

Upgrading SQL Server 2005 (momentary downtime)

Upgrading from SQL Server 2005 to SQL Server 2008 utilises database mirroring in synchronous mode to keep source and target servers synchronised. It is possible to mirror from SQL Server 2005 to a SQL Server 2008 destination. It is also supported to upgrade the mirror server from SQL Server 2005 to 2008. In either scenario, the database can failed-over with an ALTER DATABASE statement and will be brought online at the mirror in an upgraded state. Applications using SQL Native Access Client will automatically reconnect to the new principal server. This upgrade method isn't possible (or practical) for every scenario - but can deliver downtime of just a few moments - completely transparent to users!

Upgrade considerations

There are a number of aspects to consider - each method has pros, cons and gotchas. The slide deck covers some of these considerations and some further points around upgrade techniques to avoid and lessons learned from previous upgrades.

The slides I presented are available: http://coeo.com/conferences.html and here you'll also find details for Christian's session - A walk down memory lane.

Finally, big thanks to the SQL Bits organising committee who again surpassed themselves with a great venue and event that ran like clockwork, all for free - THANK YOU!

Regards,

Justin Langford.

Coeo - SQL Server Consultants | Remote DBA | Dedicated Database Engineering

FREE SQL Server 2008 ebook

2008-09-17T10:24:00Z

I've just spotted this on Greg Low's blog (http://sqlblog.com/blogs/greg_low/archive/2008/09/17/sql-server-2008-ebook-from-mspress-and-free.aspx)

Greg has contributed to a new MS Press book - Introducing SQL Server 2008, and it's available for download FREE here:

http://csna01.libredigital.com/?urss1q2we6

Enjoy!

Justin Langford

Coeo - SQL Server Consulting | Remote DBA | Dedicated Database Engineering

Windows 2008 Cluster - Validation Failure

2008-08-20T17:46:00Z

I’m a big fan of the changes to clustering in Windows 2008, especially the new cluster support policy which is a big improvement over the HCL used for cluster validation in Windows 2000 and 2003. The HCL has been replaced by a Validation Test and a supported solution requires a Windows 2008 cluster to pass a Cluster Validation Test.

I was recently building a Windows 2008 cluster to run SQL Server 2005 which consisted of two HP DL585 servers and storage provided by an HP EVA 4000 when I ran into a validation failure. The cluster validation test flagged a network configuration problem. The validation report showed a failure for the cluster based on a duplicate IP address:

Verifying that there are no duplicate IP addresses between any pair of nodes.Found duplicate IP address fe80::497:2557:ad2d%30 on node srv1.domain.com adapter Local Area Connection* X and node srv2.domain.com adapter Local Area Connection* X.

Following some research on the web - I found I wasn’t the first to experience this validation failure. The duplicate IP address is caused by Teredo which is a technology to provide IPv6 devices with Network Address Translation (NAT) over IPv4 networks. Teredo was installed but disabled in Server 2003, and Windows XP, but is enabled by default in Vista and Server 2008. Cluster validation fails because Teredo adapters have identical IP addresses - and duplicate IP addresses aren’t supported in a cluster.

Providing you don’t require NAT for IPv6 devices – the easiest way to overcome this problem is to disable the Teredo device in Device Manager (first choose ‘Show Hidden Devices’ from View menu).

There’s a recent blog post from the Cluster team and a thread Elden Christiensen mentioning this same problem.

Justin Langford

Coeo - SQL Server Consulting | Remote DBA | Dedicated Database Engineering

Why use BACKUP...WITH COPY_ONLY?

2008-06-03T17:49:00Z

New in SQL Server 2005 - this BACKUP option doesn’t appear especially interesting from the description in Books Online - however could be really handy in some scenarios. The benefit of using the COPY_ONLY option is that it doesn't break the backup chain - so won't disrupt the restore routine required for regular log, or differential backups.

Periodic refreshes of non-production environments is really useful to maintain consistency and enable testers to use live data for functional and performance testing. The time when the COPY_ONLY option could be useful is when an ad hoc full backup is required to refresh a development or pre-production environment. Typically environments refreshes are an ad hoc task - run only when needed. Often the refresh requires a backup taken from production, copied and restored to a development or test server and the .BAK file is deleted. However, if a T-SQL regular BACKUP DATABASE statement was used or a backup via Management Studio – the production database log chain will be disrupted. Without the .BAK file taken during this ad hoc backup - subsequent log and differential backup files are useless, (since they relate to the ad hoc full backup file).

The situation can only be overcome by retaining the ad hoc backup until the next full backup has completed. If a production restore were necessary, - this could add complication (and delay) as recovery documents could be inaccurate and restore scripts may not complete since the backup file name and location could be different from expected. This situation can be avoided by using the COPY_ONLY option when the ad hoc backup is initially taken. The option will take a full backup (option can also be used with log – although not with differential backups too) without disrupting the backup chain – and means any production restore processes are valid.

This backup option will is useful since it means ad hoc backups won’t affect database recoverability – and the .BAK files can be safely deleted once restored - avoiding a cluttered server!

Justin Langford

Coeo - The SQL Server Experts

SQL Server 2000 – The Obituary

2008-03-17T16:42:00Z

SQL Server 2000 was released to the public in November 2000, during its lifecycle it received four service packs (including one to resolve a major security vulnerability) and many hundreds of hotfixes. During the past 8 years SQL Server 2000 has been responsible for servicing requests from hundreds of thousands of users Worldwide, has been accountable for many billions of pounds in financial transactions and now it’s time to hang-up its locks and latches; run its last CHECKDB and compile its last execution plan.

On April 8^th 2008 Microsoft will cease mainstream support for all editions of SQL Server 2000 as the product moves into an extended support phase. After April 8^th Microsoft will no longer allow customers to raise a support case for SQL Server 2000, unless the customer has a Custom Support Agreement (CSA) in place. The support and QFE organisations within Microsoft doesn’t like CSAs since it means they have to maintain an old code base, and support multiple versions of the same product – as such they’re priced to discourage all but the most risk-averse customers! Support details can be found on the Microsoft product lifecycle page for SQL Server 2000.

Extended support means Microsoft won’t provide customers with assistance in break/ fix cases and will no longer provide hotfixes or Service Packs. SQL Server 2000 will receive security only updates for the duration of the extended support period (until April 2013). Customers must upgrade to a supported version (SQL Server 2005 SP1 or higher) in order to continue to receive support.Upgrading a database platform can be a non-trivial exercise, here are some options:

A) Do nothingYou could elect to run on an un-supported platform, providing your business is willing to sign-off on the risks. This could be viable, providing no changes are made to the application or environment – which can be difficult to ensure (or even quantify) in most modern businesses.

B) Upgrade to SQL Server 2005, running in compatibility modeThis method requires least effort and will enable you to continue to operate in an environment fully supported by Microsoft. Compatibility mode is a setting in SQL Server that allows the database engine to emulate a previous version of SQL Server – usually meaning none or minimal application changes are required and minimal risk. Unfortunately, many of the new SQL Server 2005 features will be unavailable when running in this mode.

C) Upgrade to SQL Server 2005

Bite the bullet and upgrade. Migrate your DTS packages to SSIS and take advantage of all the SQL Server 2005 features! Use this opportunity to consider 64-bit, virtualisation and consolidation. Take full advantage of online index rebuilds, two node clusters in standard edition and more efficient query optimisation.

D) Upgrade to SQL Server 2008

The latest and greatest version of SQL Server will ship in Q3 2008, with many new features and enhancements. Upgrading from SQL Server 2000 directly to SQL Server 2008 will be supported when the product ships.

At Coeo we’ve already helped a number of customers upgrade and take advantage of new features, and we’re anticipating further demand as more customers see the benefits of staying supported and migrate to SQL Server 2005.

Justin Langford
Principal Consultant
Coeo - The SQL Server Experts

HOW TO: Move a data centre

2008-02-15T08:50:00Z

My current project with Coeo is for a hosting company and their major sports brand customer. Of course, they don't actually intend moving the data centre - just the data, but this still presents some interesting challenges!

The Brief

The customer has 300+ websites and related databases with a hosting provider in mainland Europe and the project is to relocate the application, databases and network infrastructure to the UK with minimal downtime and no data loss. The servers are in separate domains, with no trust relationship and we've got a 2Mb leased line between data centres.

The Plan

The largest full database backups are around 12GB in size and testing has shown we can move a 1GB file in ~1.5 hours over the WAN. We've carried out a proof-of-concept using a custom log shipping technique to move the databases between locations. Log shipping essentially comprises three steps - backup, copy, restore and our custom log shipping incorporates compressing the files before the copy and uncompressing afterwards via a series of scripts and to allow completion within our migration window.

There are many other considerations such as security, DTS, Full Text catalogs, jobs, maintenance plans including operators and alerts - each of which should be reviewed and migrated if required.

The Availability Challenge

There's often a lack of precision around cutover times and availability when DNS is involved, even if TTLs are reduced - it can still be a challenge to pinpoint propagation times and ensure a consistent experience for all users from a network perspective. We're currently going through a number of dry-run iterations of the backup/ compress/ copy/ uncompress/ restore cycle to ensure the process is robust and timings for cutover are predictable.

In any migration project there has to be a trade off during cutover between data loss and data availability. Using the log shipping technique outlined above, we bring the database online in the new data centre by restoring a tail-log backup using the WITH RECOVERY option. However, what the status of the original database from the point the tail-log backup was taken?

There are three options: take database off-line, put database into read-only mode or continue to allow access (no change). The first two options would ensure data is consistent between the two sites since no further changes will be allowed to the original database - the impact to users largely depends on whether the application can cope with read-only access to a database. The third option is more interesting in a web hosting scenario because most of these sites are effectively online catalogs (not ecommerce), therefore the sites benefit from 100% uptime as they can afford some minimal data loss during the cutover. Most online marketing or e-catalog oriented websites write to a database for usage statistics, logging search terms, trend analysis and enquiries etc. Essentially, no money is lost if transactions are written that are subsequently not present post-cutover.

The amount of data lost can be quantified as the time between the tail-log backup being taken, and the DNS changes taking effect to redirect visitors to the new servers. Providing this is known and managed, this is acceptable in most scenarios where uptime is more important than data loss. Given sufficient application and schema knowledge, the data could be exported and loaded into the new server after cutover if required (either via bcp or DTS), this option isn't practical in this scenario because of the number of databases and timeframes for cutover.

The Conclusion

There are a number of feasbile solutions satisfying the business requirements and it's important to clearly communicate the pros and cons of each solution to enable the business to make the right decision.

We're still working on dry-run testing, but intend to use a combination of continued write access to databases for catalog sites and one of the other methods for sites where transactional consistency is required (choosing an appropriate approach in each case).

The migration plan may consist of a combination of solutions - each fit for purpose and selected for their characteristics. Building a thorough migration plan and testing it meticulously will reduce risk and allow predictable timings.

Justin Langford
Principal Consultant
http://coeo.com - The SQL Server Experts

New book - SQL Server 2005 Performance Tuning

2008-02-13T20:13:00Z

Last summer I worked with a small groups of engineers, administrators, developers and Wiley Publishing to put together a book focused on performance tuning in SQL Server 2005. The book is the culmination of thoughts and experiences from a diverse group of authors and provided me with a great introduction to professional technical authoring.

The book is intended to provide server engineers with the necessary knowledge required to carry out performance analysis and tuning including many automated tasks for gathering and analysing data from database servers.

I hope the chapters I contributed will be useful in raising the profile of tools such as Performance Analysis of Logs (PAL - http://www.codeplex.com/PAL) and illustrating how loading perfmon data into a database can simplify analysis of large data volumes.

The book has is part of the Wrox IT Professional series and has just been released, and is available on Amazon here: http://www.amazon.co.uk/Professional-Server-2005-Performance-Tuning/dp/0470176393/ref=pd_bbs_sr_1?ie=UTF8&s=gateway&qid=1202774647&sr=8-1

I hope the book proves useful, provides useful content to readers and is enjoyable to read! I'll be interested to hear any feedback too!

Regards,

Justin Langford
Principal Consultant
http://coeo.com - The SQL Server Experts

First Post: Microsoft to Coeo

2008-01-21T15:38:00Z

Hello,

I've worked as a Premier Field Engineer for Microsoft in the UK for three years and I've just left Microsoft to join Christian Bolton (http://sqlblogcasts.com/blogs/christian) at Coeo (http://www.coeo.com) - a Microsoft Partner focussed on SQL Server.

During the past three years I worked on many interesting and varied engagements for some of Microsofts' biggest enterprise customers in Europe including retail and merchant banks, government departments and BT where I worked on their IPTV project. My primary role was supporting customers in the "Operate and Optimise" phase of the IT lifecycle, although this frequently extended into other areas including architecture and testing as needed.

I'm very excited about joining Coeo and I'm looking forward to SQL Server 2008 launch next month!

Justin