How can I convert a string to "proper" case in SQL Server?

A. SQL Server has no built-in function for this and neither does it support user-defined functions. Therefore your two choices are :-

1. Write an extended stored-procedure
2. Write a TSQL stored-procedure

An XP is faster and lets you have the full range of C programming tools and techniques, however it is possible to implement a simple example in TSQL. 

If you only have a surname to update in a single field, then it is possible to do it in a single update statement. Example below.

UPDATE
SET surname = substring(surname,1,1) + lower(substring(surname,2,(datalength(surname)-1)))

The below is an example of an sp. It only handles simple cases, and won't do stuff like D'Arcy properly - if you want full function stuff you are recommended to write your own generic C routine and then call it from an XP.

create procedure sp_proper
@in varchar(255) output
as
BEGIN
declare @in_pos tinyint,
@inter varchar(255),
@inter_pos tinyint

select @in_pos = 0,
@in = lower(@in)
select @inter = @in
select @inter_pos = patindex('%[0-9A-Za-z]%', @inter)
while @inter_pos > 0
begin
select @in_pos = @in_pos + @inter_pos
select @in = stuff(@in, @in_pos, 1, upper(substring(@in, @in_pos, 1))),
@inter = substring(@inter, @inter_pos + 1, datalength(@inter) - @inter_pos)
select @inter_pos = patindex('%[^0-9A-Za-z]%', @inter)
if @inter_pos > 0
begin
select @in_pos = @in_pos + @inter_pos
select @inter = substring(@inter, @inter_pos + 1, datalength(@inter) - @inter_pos)
select @inter_pos = patindex('%[0-9A-Za-z]%', @inter)
end
end
END 
go