Billhighway.com – growing again!

At Billhighway, we’re committed to not participating in this economic slowdown, and are currently looking to fill several new positions among our development team, including: 1.) strong .net developers, 5+ years of experience; 2.) QA folks (junior and/or senior); and 3.) a certified Scrum Master.

Billhighway has 20ish employees and growing rapidly.  We create world-class financial software for membership-based communities, serving 100’s of thousands of customers throughout North America. Think of mashing up QuickBooks, PayPal & SalesForce.com and you’d have a good basis for what we’re all about.  We employ the latest tools (VSTS, TFS) technologies (.NET, AJAX, SQL, SOA & grid-computing) and methodolgoies, such as Agile.

If you’re seeking a new challenge, or want to be part of a fast-paced, pure technology company – check us out: http://www.billhighway.com/aph/marketing/careers.aspx

How to Recruit Developers

Thought this was worth sharing..

SQL 2005 Cluster Startup Params

Having recently deployed a new SQL 2005 cluster, I thought I’d share a couple things that I originally got hung up on. 

First, startup parameters, It took me some time to find that when more than one parameter is used as Startup Parameters in SQL Sever, they should be separated by semicolon rather than space.

For example:
-dC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mastlog.ldf;-T4616

Other, BIG lesson learned, is when moving SQL 2005 System Databases, you’re again required to update startup params.  If you happen to make a mistake, there’s a small hoop you have to jump through to remedy the problem, as simply fixing the startup parameters won’t recover the SQL Service, as it simply won’t restart.  In a clustered environment, when you change a protocol parameter for a clustered instance of Microsoft SQL Server 2000 or 2005 to a value that is not valid, the SQL Server service cannot start. Additionally, you cannot change the protocol parameter back to the original value.

This problem occurs because the cluster service obtains an incorrect protocol setting from a cluster checkpoint when you start the SQL Server service.  Use the steps below for a work around:

1. Bring the SQL Server resource offline by using Cluster Administrator.
2. At a command prompt, run one of the following commands to disable the cluster checkpoint for the specific registry subkey:
   

   •	For an instance of SQL Server 2005, run the following command: cluster res “SQL Server” /removecheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER” Note In this command, MSSQL.x is a placeholder for the instance ID for the instance of SQL Server. You can determine the corresponding value for the system from the value of the MSSQLSERVER registry entry in the following registry subkey: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\
   •	For a default instance of SQL Server 2000, run the following command: cluster res “SQL Server” /removecheck: “Software\Microsoft\MSSQLServer\MSSQLSERVER”
   •	For a named instance of SQL Server 2000, run the following command: cluster res “SQL Server” /removecheck: “SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLSERVER” Note In this command, InstanceName is a placeholder for the name of the SQL Server 2000 instance.

    

3. Correct the protocol parameter for the clustered instance of SQL Server on all nodes.
4. At a command prompt, run one of the following commands to enable the cluster checkpoint for the specific registry subkey:
   

   •	For an instance of SQL Server 2005, run the following command: cluster res “SQL Server” /addcheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER”
   •	For a default instance of SQL Server 2000, run the following command: cluster res “SQL Server” /addcheck: “Software\Microsoft\MSSQLServer\MSSQLSERVER”
   •	For a named instance of SQL Server 2000, run the following command: cluster res “SQL Server” /addcheck: “SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLSERVER”

    

5. Bring the SQL Server resource online.

Happy Recovery!

Moving SQL 2005 System Databases

While in the process of upgrading our database servers, I had to recall how to move system databases, which forced me to update my notes, from sql 2000.  Below are the steps required to move sql 2005 system databases.  If you’re wondering why this is needed, well there’s a few reasons.  First, if you don’t select the target location during SQL install, these files can be put under the default installation path, typically under the C:\ drive.  In our world, optimization is everything, therefore we attempt distribute data, logs, tempDB on their own spindles with varying RAID levels to maximize performance, which is a whole other topic.

NOTE:  These steps are slightly different if you’re working with clustered databases, which I’ll speak to in a future post.

MODEL:
To move the MODEL database, you must start SQL Server together with the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database.

1. In SQL Server Configuration Manager, click SQL Server 2005 Services.

2. In the right pane, right-click SQL Server (<instance_name>), and then click Properties.

3. On the Advanced tab, in the Startup Parameters box, type the parameters separated by semicolons (;). i.e. -c; -m; -T3608

For example, to start in single-user mode, insert -m; in front of the existing startup options, and then restart the database.

Important: After you are finished using single-user mode, you must remove the -m; from the Startup Parameters box before you can restart the server instance in the normal multi-user mode.

4. Stop and then restart SQL Server.

5. Detach the model database by using the following commands:

use master
go
sp_detach_db ‘model’
go

6. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder to the desired location.

7. Reattach the model database by using the following commands:

use master
go
sp_attach_db ‘model’,’R:\sqlData\model.mdf’,’T:\sqlLogs\modellog.ldf’
go

8. Remove -c -m -T3608 from the startup parameters in SQL Server Configuration Manager.

9. Stop and then restart SQL Server. You can verify the change in file locations by using the sp_helpfile stored procedure. For example, use the following command:

use model
go
sp_helpfile
go

 

MSDB:
To move the MSDB database, you must start SQL Server together with the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database. To add the -c option, the -m option, and trace flag 3608, follow the steps in the “Moving the model database” section. After you add the -c option,  the -m option and trace flag 3608, follow these steps:

1. Stop, and then restart SQL Server.

2. Make sure that the SQL Server Agent service is not currently running.

3. Detach the msdb database as follows:

use master
go
sp_detach_db ‘msdb’
go

4. Move the Msdbdata.mdf and Msdblog.ldf files from the current location to the new target location.

5. Remove -c -m -T3608 from the startup parameters in SQL Server Configuration Manager.

6. Stop and then restart SQL Server.

Note If you try to reattach the msdb database by starting SQL Server together with the -c option, the -m option, and trace flag 3608, you may receive the following error message:

Server: Msg 615, Level 21, State 1, Line 1 Could not find database table ID 3, name ‘model’.

7. Reattach the msdb database as follows, changing “X” for your target location:

use master
go
sp_attach_db ‘msdb’,’X:\sqlData\msdbdata.mdf’,’X:\sqlLogs\msdblog.ldf’
go

 

MASTER: Moving the master database

1. Change the path for the master data files and the master log files in SQL Server Configuration Manager. Note You may also change the location of the error log here.

2. Right-click the SQL Server in Enterprise Manager and then click Properties.

3. Click Startup Parameters to see the following entries:

-dX:\data\master.mdf
-eY:\errlog\ErrorLog
-lZ:\logs\mastlog.ldf

-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log file.

4. Change these values as follows:

a. Remove the current entries for the Master.mdf and Mastlog.ldf files.

b. Add new entries specifying the new location:

-dX:\SQLDATA\master.mdf
-lZ:\SQLDATA\mastlog.ldf

5. Stop SQL Server.

6. Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).

7. Restart SQL Server.

tempDB: Moving the tempdb database

You can move tempdb files by using the ALTER DATABASE statement.

1. Determine the logical file names for the tempdb database by using sp_helpfile as follows:

use tempdb
go
sp_helpfile
go

The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.

2. Use the ALTER DATABASE statement, specifying the logical file name as follows:

use master
go
Alter database tempdb modify file (name = tempdev, filename = ‘S:\tempDB\tempdb.mdf’)
go
Alter database tempdb modify file (name = templog, filename = ‘T:\sqlLogs\templog.ldf’)
go

You should receive the following messages that confirm the change:

Message 1
The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.

Message 2
The file “templog” has been modified in the system catalog. The new path will be used the next time the database is started.

3. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.

4. Stop and then restart SQL Server.

 

– Hope this helps save you some time next time you need to move system databases around.