@@SERVERNAME is NULL & SQL Server Replication

Issuing the following query should return the server name of the instance you are working with. In the scenario that is about to be described, the server had some work done on it which left the query below returning NULL.

This causes problems when attempting to run tools such as the replication monitors and wizards as they appear to use this query internally and thus complain that a connection cannot be made to a (NULL) server during the login process. Also various other errors start occurring using Server Management Studio.

SELECT @@SERVERNAME
 
As the sever had been used for replication in the past, there was a node under linked servers for itself. The server was acting as its own distributor in replication, thus had a “replication” database under System Databases node in Management Studio.
There was also a Linked Server named repl_distributor that I take to be a pointer to the distributor, in this case the same server.
 
The problem was the the Repliation >> Local Publications  node contained a publication that was not working.
 
Trying to run any commands resulted in the server complaining about the null server name, by the way the following command returned the correct server name;
SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY ('InstanceName')

It seems this is held more up to date than the other property and syncs with the sever name better.
 

Change @@SERVERNAME

All the server names “registered” can be viewed with

sp_helpserver

In the returned table it was possible to see the Linked server, but there was no server with the ID column of 0. The row with the ID column of 0 is the server name returned when the @@SERVERNAME property is used. To set this add a server with the following;

sp_addserver '<YourServerName>','local'

The local parameter tells it that you are talking about this special server name. However attempting this in this case resulted in the server complaining that the server already existed, due to the LINKED server.

It should be possible to drop the existing linked server by issuing the following command;

SP_DROPSERVER '<YourServerName>','droplogins'

This didn’t work as it complained that the server was acting as a publisher. Argh!

Temporary Change to @@SERVERNAME

In order to “getinto” the SQL tools I creating a ficticous servername, adding that servername to the local hosts file of the server.

 
sp_addserver '<YourServerName>TEMP','local'

In the hosts file C:\WINDOWS\system32\drivers\etc\hosts

127.0.0.1       localhost
127.0.0.1    <YourServerName>TEMP

YOU MUST restart the SQL server service for the @@SERVERNAME variable to refresh, easy to forget!

So now @@SERVERNAME returned a false servername, however at least working on the server I could use Management Studio to connect to the server under this fake name and start accessing normal tools for replication.

Removing replication

 

At this point the aim was to totally remove replication and then add it all back in once it was all gone. To do this there is a guidance document How to manually remove a replication in SQL Server 2000 or in SQL Server 2005this seemed to work for 2008 too.

For each replicated database, this was used to rip out all replication from the databases.

exec master.dbo.sp_removedbreplication '<replicated_databse_name'
sp_dropdistributor @no_checks=1, @ignore_distributor=1
 
The rest of the commands in this section are a medley of commands that can be used to get to the point where the replication is gone.
 
To drop the replication distribution database use this;
alter database distribution set offline
drop database distribution
 
master.dbo.sp_serveroption @server=N'<YourServerName>', @optname=N'dist', @optvalue =N'false'



Helpful help functions the last two seemed to be the final step needed in this case to allow the dropping of the various objects (linked server and replication database).

sp_msrepl_check_server
sp_helpdistpublisher
sp_get_distributor
sp_helpdistributor
sp_helppublication
select * from msdb..msdistpublishers
delete from msdb..msdistpublishers
 
“There are still remote logins or linked logins for the server” - one biggest breakthrough with all this was the clue to try right clicking on the object you are trying to destroy, scripting it and then using that to get the server names for the above manually removing replication method and the other commands listed. It also gives you the scaffold to change the option values to false for the linked servers. See the following fragment that I hacked to get our options for replication and publication set to false after using the scripting technique.
 

/****** Object: LinkedServer [<YourLinkedServerName>] Script Date: 02/18/2011 02:43:09 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'<YourLinkedServerName>', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_droplinkedsrvlogin @rmtsrvname=N'<YourLinkedServerName>',@locallogin=NULL

@useself=N'False',@locallogin=NULL,@rmtuser=N'webreplication',@rmtpassword='########'
EXEC master.dbo.sp_droplinkedsrvlogin @rmtsrvname=N'<YourLinkedServerName>',@locallogin=NULL

@useself=N'False',,@locallogin=NULL,@rmtuser=N'webreplication',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'<YourLinkedServerName>', @optname=N'dist',

@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<YourLinkedServerName>', @optname=N'data access',

@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'<YourLinkedServerName>', @optname=N'sub', @optvalue=

N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'<YourLinkedServerName>', @optname=N'pub', @optvalue=

N'false'


Changing @@SERVERNAME to what it should be

Now it was possible to change the servername back to what is should have been in the first place, and remember to delete the entry in the hosts file.

SP_DROPSERVER '<YourServerName>TEMP','droplogins'
sp_addserver '<YourServerName','local'
 
YOU MUST restart the SQL server service for the @@SERVERNAME variable to refresh, easy to forget!

sp_helpserver

Looking and help server should now return the server names with the actual servername having an ID of 0.

SQL Jobs

 
Don’t forget to delete all the sql replication jobs and agent jobs.
 
 
Thus after a few hours the server now has no replication and it is possible to start again.

GoogleMini Search Appliance Fails to Boot

Security screwA sinking feeling washes over as the network monitor alerts you to a lost server, a bit like loosing contact with one of the fleet at sea. Today it is the GoogleMini, purchased to quickly give us good, familiar search results to our externally facing websites a couple of years ago.

Support Policy

The GoogleMini comes with a maximum of two years support. After that time, if support renewal is required then you must accept a new GoogleMini to go with it. A free box with your support contract is better way of thinking of the way Google run the support and less painful than thinking about how every two years another 1U sever is going to be scrapped. The typical response to cries for help from Google on older boxes is;

Hello **********,

Thank you for writing to Google.

The technical support and hardware warranty for the Google Mini can be extended to up to a maximum of two years. Since your device is past the two year mark, you would need to purchase a new Google Mini if you wanted to be under support again.

However, you can still use the Google Mini to provide the most relevant search results in the industry to your users. The Google search technology comes under a perpetual license, so you are free to keep using it for the life of the hardware.

This support arrangement makes the total cost of ownership high for this solution, but the solution in a box is attractive and hard to resist as it leaves the development and IT infrastructure teams free to concentrate on innovation.

Broken GoogleMini

So what of the alert, you ask? It turns out that our faithful server that has ran without restart for two years has stopped working. On reboot it makes a lot of noise from the fans but shows a blank, black screen on the monitor and no beeps or any BIOS screen.

Although the server is out of warranty the licence terms probably prevent us from continuing to use once it has been opened up. Hence for us the only option is to buy a new GoogleMini or find another solution to serving search results.

Curiosity and opening up the GoogleMini

ATTENTION: Do not open your GoogleMini if you intend to keep using it and/or it is covered still by your support contract.

Inside GoogleMiniCuriosity reigned and off came the rack mount cheeks, out came the tamper proof screws. The all covering blue Google sticker was peeled off the top of the unit to allow the top to slide off.

A quick note on the two screws in the back. The tamper proof screws can be opened by sawing a slot into them for a normal flat head screwdriver to use (gentle use of hacksaw), or alternatively some claim that a small flat head screwdriver wedged into the screw will allow it to be undone.

Broken memory bank on motherboard

Inside you find a familiar server layout. thanks to a post on the internet, the memory was removed and now the server started doing POST beeping, ah-ha, not so dead! Putting memory back into memory bank 1 alone allowed the server to start booting. Putting any memory into the memory bank 2 caused it to not boot again. Looks like a broken mother board. New memory was loaded into the first bank only. After doing a lot of file system checking and some half an hour later, it is running.

Apparently the hardware inside does vary a little, this one was a P8SCT motherboard, marketed by SuperMicro. I guess in order to regain faith in the server, ordering a new motherboard would allow breathe some life into it.

Repurpose the hardware

So challenge of fixing the server hardware problem is resolved but it must now be repurposed for a new life as a Linux server for other uses as it has been opened. There are a few postings people who have very successfully done this with the GoogleMini, seems like the right thing to do from an environmental point of view. The kit is a dual Pentium 3 so should have plenty to give to other deployments, and importantly it looks cool in blue.

To do anything with it, you must get into the BIOS, that is normally protected with a Google owned password, the following snippets help get around that one;

[4]

Step 3 — Resetting the BIOS
Different Google Minis come with different internals. This one happened to have a single processor Super Micro ComputerSUPERO P8SCT motherboard. Other Minis have come with dual processor Pentium 3 boards. In our case, to clear the CMOS and eliminate the BIOS password requirement, I had to bridge two contact pads with a screwdriver. Close everything up, power on the machine, hit the DEL key and straight into the BIOS we go.

[5]

clear the BIOS by jumping the JBT1 pins according to the motherboard manual page 2-19, chapter 2-7 (Jumper Settings). Remove Power and AC coard, short-circuit a few seconds with a small screwdriver, job done.

Cloud based search

So where from here? There may be cloud based search solutions now offered, however the cost of bandwidth in and out of the data centre would be unbearable to support external indexing, for the very large sites this box was indexing. The Mini, sat next to the webserver in the same rack at the Co-Lo seems to make sense.

References:

[1] How do I salvage an old Google search appliance after an apparently failed BIOS update

[2] How to Turn a Google Mini into a Home Server

[3] AnandTech Search goes Google -This post provides some great photographs and information on how to get into your GoogleMini.

[4] Google Mini – Can’t Boot Wont Boot 

[5] HACKING THE GOOGLE MINI

[6] Support Policy

The Google Mini is offered as a perpetual license, including one year of support and hardware replacement coverage, for a total price of $1995 for search across 50,000 documents. Additional versions deliver search across 100,000 documents for $2,995, 200,000 documents for $5,995, and 300,000 documents for $8,995. A second year of support and hardware replacement coverage is available for $995.

Google Mini Front

Dynamically fetching column names and meta descriptions TSQL

Some time ago I made to some large width SQL server tables providing for lots of spare fields consisting of various data types. These tables could be used for system stakeholders to add new things they needed to store against products in our ERP system over time without disturbing me for more fields. The SQL server description property for the column was used to populate the labels on the GUI forms dynamically with the meaning of the field. Thus there is no need for a separate table to hold the label information and that also means that the information is at hand when working in SQL.

At the time I used a TSQL snippet from the web to grab the descriptions. The snippet used the sys.columns, sys.extended_properties to gather the info required to for the GUI labels. The query has been causing problems over the years, running for up to 2 seconds is not uncommon. After finally getting a reason to revisit the forms, I went to look at the query plan for and uncovered the cause. A scan due to the where clause that contained WHERE OBJECT_NAME(c.object_id)=@TableName

To avoid calling the OBJECT_NAME function for each comparison row in the DB, I added the sys.objects table to create a lookup join that fetches the object id for the table owning the columns and now the query is running in sub-second time spans. This is a great improvement, resulting in a snappy GUI form open, unlike the sluggish experience the users were getting previously. This points out again how you have to take care calling functions for each row as it expensive, set operations almost always are the way to go!

The resulting improved stored procedure containing the optimised query for my reference is below. I wish I could reference and acknowledge the originator of this query, but it was in the days before I grew wise enough to retain references to the origin of code snippets in my TSQL comments.

TSQL  to retrieve table column names and meta description for a SQL server table

 
CREATE PROCEDURE [SY_GetTableFields]
(@TableName as varchar(255))
AS
 
IF CAST(SUBSTRING(CAST(SERVERPROPERTY('productversion') 
  as varchar),1,1) as int) >= 9
BEGIN      -- This is a SQL 2005 machine      
SELECT    [Table Name] = OBJECT_NAME(c.object_id),      
        [Column Name] = c.name, 
        [Description] = ex.value, c.object_id
        FROM  sys.objects sobj
            JOIN sys.columns c 
                ON c.object_id = sobj.object_id       
            LEFT JOIN   sys.extended_properties ex
                ON ex.major_id = c.object_id           
            AND ex.minor_id = c.column_id 
            AND OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 
            AND ex.name = 'MS_Description' 
        WHERE  sobj.name= @TableName
        ORDER  BY  c.column_id
END
ELSE
BEGIN
SELECT 
    [Table Name] = i_s.TABLE_NAME, 
    [Column Name] = i_s.COLUMN_NAME, 
    [Description] = s.value 
FROM 
    INFORMATION_SCHEMA.COLUMNS i_s 
    LEFT OUTER JOIN 
    sysproperties s 
ON 
    s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME) 
    AND s.smallid = i_s.ORDINAL_POSITION 
    AND s.name = 'MS_Description' 
WHERE 
    OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME),
   'IsMsShipped')=0 
    AND i_s.TABLE_NAME = @TableName 
ORDER BY 
    i_s.TABLE_NAME, i_s.ORDINAL_POSITION
 
END

Upgrade from Team Foundation Sever 2005 to 2010 (TFS)

 

What a pleasant surprise I had upgrading our Team foundation server 2005 to 2010.

What I did, this was me stumbling through the process so if you want a more formal approach follow the many documents online:

  • Took a VM ware snapshot of the server (SQL and Application Tier)
  • Ran uninstaller for from Add/Remove programs of the server for anything TFS
  • Ran installer for windows share point 3.0 and allowed it to upgrade in place
  • Upgraded SQL server 2005 to 2008 in place
  • Moved the virtual server to ESX from VMWare Server for 2G memory requirement at install
  • Ran installer for Team Foundation Server
  • Ran configuration wizard for Team Foundation Server

 

Now I have a super fast TFS 2010 server! I do so thank everyone who worked to make this so painless. In my mind I had set aside a week of pain to get this job done, as it happened it all took about a day, most of which was waiting for installers and converting the VM machine (needed a disk resize).