SQL 2005 SP3 Released

SQL Server 2005 SP3 is now available
http://www.microsoft.com/downloads/details.aspx?familyid=AE7387C3-348C-4FAA-8AE5-949FDFBE59C4&displaylang=en

Microsoft Beta Exams

Recently I sat a beta exam for SQL Server 2008. The exam was
70-451 PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008

For anybody interested in sitting beta exams, they can be a little rough around the edges. Some of the questions can be ambiguous and this particular exam was quite long.

On the upside, they are free to sit and count in full if you pass. Unlike the regular exams you will receive your results later on once the exams go mainstream

Beta exam announcements are here
http://blogs.technet.com/betaexams/

Ben

Writing Applications That Benefit From Database Mirroring

One of the most useful high-availability features introduced in SQL Server 2005 is without doubt database mirroring. Some of the features that stand out when you compare this technology with database clustering include:

• Failover time can be sub-second depending on the configuration you are using. We are talking much faster than a cluster failover
  
• You have the option of using synchronous or asynchronous commits across the two servers:
  
  • When you use synchronous mirroring, control doesn't return to the client until a commit happens on both the principal and mirror databases
    
  • When you use asynchronous, SQL Server returns to the client in the usual fashion and commits on the mirror when it can. Of course this can lead to some potential for data loss in the event of a failover
    
• The buffer cache on the mirror server is kept "hot". That is to say that recently used pages on the principal server are also present in the buffer cache on the mirror server. The benefit here is that the redo time will be shortened in the circumstance that a failover occurs. Also users are less likely to notice a performance hit.
  
• You can use database snapshots on the mirror database for reporting purposes
  
• You can implement mirroring with cheap hardware.
  
• Easier to configure than log shipping
  

So far I have seen quite a few implementations where customers are using asynchronous mirroring with manual failover. Using the witness role is yet to gain popularity based on my experience.

Wiring up the Client

Perhaps the main drawback with mirroring when compared to clustering is that the client needs a little extra intelligence when it comes reconnecting after a failover. The diagram below (from MSDN) shows how the client connects initially.

Take a look at the connection string below. You will notice that there is an extra bit which specifies the IP address and port of the Failover Partner.

"Server=148.182.197.186,1433; Failover Partner=148.182.197.186,1234; Database=MirrorTest; Network=dbmssocn; Integrated Security=SSPI";

The SQL Native Client or .NET Data Provider for SQL Server will initially try to connect to the server specified in the Server part of the connection string. If it can't connect, it will try the failover partner name. If the failover partner is in fact the current principal server, then the client recognises this and downloads the server/instance name of the current mirror server and stores it in memory, overwriting the value provided by the connection string.

Of course if the principal database is offline, and the mirror is yet to be promoted then the client will fail to connect. This situation is highly likely in the event that a witness server is not being used to facilitate automatic failover.

Client Behaviour during a Failover

If a client is connected when a failover takes place, the client unfortunately has to be smart enough to attempt to reconnect. This is a bit of a drawback for legacy applications, which might need a restart.

I wrote a few lines of C# to see how long a manual failover would knock my test client off the air for.

public string GetData()
{

try
{
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("SELECT @@SERVERNAME", conn);
da.Fill(ds);

return DateTime.Now.ToString("hh:mm:ss") + ": " + ds.Tables[0].Rows[0][0].ToString();

}
catch (Exception ex)
{
string retVal = "Connection Failed. Retrying Connection...";

conn = new
SqlConnection();
conn.ConnectionString = connstring;

try
{
conn.Open();
}
catch( Exception x )
{
retVal = "No Connection...";
}
return retVal;
}
}

I just set up a timer that called this method every 100 milliseconds, and it outputs something like the following:

Then I issued the following command on my principal server. (Note you have to be in the master database to issue this command):

ALTER DATABASE MIRRORTEST SET PARTNER FAILOVER

And viola! My client reconnects to the new principal server within 100ms

Database mirroring is an exciting technology, and I would encourage people to consider using it as an underlying high-availability platform when building applications.

Scripting System Objects in SQL 2000

Imagine you had a SQL 2000 system where a developer thought it was a good idea to put random stored procedures in the master database. Now imagine that the same developer was extra creative and decided to modify system stored procedures and not tell anybody.

The sysobjects table has a column that tells you when an object was created, but unfortunately it doesn’t tell you when it was last modified.

So to find the objects which have been modified, you will need to extract them and compare them to a virgin system.

Generating a SQL script isn’t as intuitive as you might think – in fact you can’t script system objects using Enterprise Manager.

Here’s a snippet which will build a script to extract the actual text of all the system stored procedures

select ' exec sp_helptext ''' + name + '''' from sysobjects

where xtype='P'

order by name

If you run this, it will build a script similar to the following

exec sp_helptext 'sp_ActiveDirectory_Obj'

exec sp_helptext 'sp_ActiveDirectory_SCP'

exec sp_helptext 'sp_ActiveDirectory_Start'

exec sp_helptext 'sp_add_agent_parameter'

exec sp_helptext 'sp_add_agent_profile'

exec sp_helptext 'sp_add_data_file_recover_suspect_db'

exec sp_helptext 'sp_add_log_file_recover_suspect_db'

exec sp_helptext 'sp_add_log_shipping_alert_job'

exec sp_helptext 'sp_add_log_shipping_primary_database'

exec sp_helptext 'sp_add_log_shipping_primary_secondary'

exec sp_helptext 'sp_add_log_shipping_secondary_database'

exec sp_helptext 'sp_add_log_shipping_secondary_primary'

exec sp_helptext 'sp_addalias'

... and so on

Run the resulting script on the modified system and a vanilla system, and compare the results in windiff.

From here you will find your modified procs pretty quickly.