Blog ala Vidar

SQL, AppFrame and other cool technologies

Main Database Server Upgrade 2

We are now finished upgrading. Because of missing SCSI-controller, we couldn’t set all the disks up as we wanted, but as soon as the controller is up and running, tempdb and LDF will be moved to separate arrays. The new server has the double amount of both CPUs and memory, and the disks it’s running on is quicker. We did some quick performance tests, and it looked VERY promising. Although we have to wait till Monday to see the actual performance (because we were the only users logged on).

We did some other minor things, like add more memory to TSQLDEV1 and add a new DHCP-range.

For server monitoring we’re using a tool called “What’s up”. 30 seconds after we unplugged the old server, all of us (me, JanCB, Leif and Åsmund) got SMS that it was down. We’ve got monitoring on ALL servers in our network. Nice to see that it’s working 🙂 We also got Securitas monitoring our main server room, in case the power shuts down. To test this, we used a hair dryer on the censor. The censor is set up to alarm Securitas if it exceeds 40 degrees Celsius. We heated it up to 42, and 5 minutes later, JanCB got a phone call from Securitas saying our server room was over heated! Before we got this monitoring, we only had “what’s up” to notice us. But what happens if the power shuts down? Well, all servers run on UPS’s. After some time, the battery in the UPS’s is empty, so the server shuts down. What happens then? The “what’s up” server is down, so it can’t notice us. One extra cool “feature” we didn’t know was that after we heated the censor to 40 degrees, the alarm in the building started to yell. We also got messages from another monitoring system that the noise level in the server room was to high 😛 SWEEEEEET!

After everything was set up, we did some tests. Teamdoc, internal applications, MarketMaker etc. Everything seems to be working. There’s one SP that was causing troubles though. sstp_Database_Maintenance_RefreshAllViews. This works perfectly in 2005, but in 2008 I had to add ROLLBACK TRANSACTION inside the catch-block. If not, it “thought” that all views failed for some weird reason. I’ll read more about it tomorrow. Have to get some sleep now. Here’s the code:

DECLARE @ViewName NVARCHAR(128)
DECLARE @ViewsToRefresh TABLE (
	ViewName NVARCHAR(128),
	TSQLCommand NVARCHAR(MAX) NULL
)

INSERT INTO @ViewsToRefresh (ViewName)
SELECT [name]
	FROM sys.sysobjects
	WHERE LEFT([name], 4) IN ('atbl', 'stbl',
				'arpt', 'atbv', 'atbx', 'aviw',
				'stbv', 'stbx', 'sviw')
	  AND xtype='V'
	ORDER BY [name]

WHILE EXISTS (SELECT * FROM @ViewsToRefresh)
BEGIN
	SELECT TOP 1
	  @ViewName = ViewName
		FROM @ViewsToRefresh

	BEGIN TRY
		EXEC sp_refreshview @ViewName
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
		PRINT 'Error refreshing ' + @ViewName
	END CATCH

	DELETE
		FROM @ViewsToRefresh
		WHERE ViewName = @ViewName
END
Advertisements

Comments are closed.

%d bloggers like this: