Table Valued Parameters

There is one new feature in SQL Server 2008 that I am pretty excited about, that is the ability to pass table valued parameters onto stored procedures or functions. One scenario where this could be handy is when we have to insert multiple rows of data. For example, let say you have an application where you allow users to input multiple new employee information at once and you need to insert all those information into the employee table. Prior to SQL Server 2008, we probably would loop through each row (employee information) and call the stored procedure that insert the information to the employee table one row at the time. Alternatively we could parse those employees information into XML and pass the XML into a stored procedure where it will then process the XML and insert the information to the employee table. But now with SQL Server 2008, we could pass the new employees information as a table valued parameters into a stored procedure, which can make things simpler.

You might ask why not use bulk insert or bcp. According to Microsoft, in many cases, if the number of rows is less than 1,000, using table valued parameters actually might be better performance wise.

How to Use Table Valued Parameters

It is pretty simple actually, You just need to do the following:

1. Create a user defined table type. This actually defined the structure for the table valued parameters.
2. Create the stored procedure or user defined function that would utilized the user defined table type (created on Step 1) as its parameter.
3. You can now pass table valued parameters to the stored procedure or user defined functions created on Step 2.

Example

Let say that I have an table called EmpInfo. I created the table using the following SQL Script:

CREATE TABLE dbo.EmpInfo (
    EmpID INT IDENTITY(1,1) PRIMARY KEY,
    EmpFirstName VARCHAR(25),
    EmpLastName VARCHAR(25),
    EmpTitle VARCHAR(25),
    CreatedDate    DATETIME);

Then I created a user defined table type called EmpInfoTableType using the following SQL Script:

CREATE TYPE dbo.EmpInfoTableType AS TABLE (
    EmpFirstName VARCHAR(25),
    EmpLastName VARCHAR(25),
    EmpTitle VARCHAR(25));

I now can create a stored procedure that utilized EmpInfoTableType user defined table type as one of its input parameter by using the following SQL Script:

CREATE PROCEDURE dbo.InsertEmpInfoBatch (@EmpInfoBatch EmpInfoTableType READONLY)
AS
BEGIN
    INSERT INTO EmpInfo (EmpFirstName, EmpLastName, EmpTitle, CreatedDate)
    SELECT EmpFirstName, EmpLastName, EmpTitle, CURRENT_TIMESTAMP
    FROM @EmpInfoBatch
END
GO

If you notice there is READONLY keyword when declaring the EmpInfoBatch parameter (table valued parameter). This is required for table valued parameters (or more of a restriction actually), as we can not do any data modification to the table valued parameters within the stored procedure and user defined function.

Now the fun part, we test it:

DECLARE @EmpInfoTable AS EmpInfoTableType; 

INSERT INTO @EmpInfoTable 
VALUES ('John', 'Doe', 'CEO'),
('Jane', 'Smith', 'CFO'),
('Mike', 'Fox', 'CTO') 

EXEC dbo.InsertEmpInfoBatch @EmpInfoTable;

If we issue the following SQL Script:

SELECT EmpID, EmpFirstName, EmpLastName, EmpTitle, CreatedDate
FROM dbo.EmpInfo

We should get the following result:

Pretty simple, eh?

Hyper-V Clustering: Guest Connection Problem

Virtualization is pretty neat. It allows you to consolidate several servers that you have into fewer servers, this will allow us to cut down on power consumption (make us more green and save us some money). For server virtualization, Microsoft has Hyper-V. With Windows Server 2008 and Windows Server 2008 R2, we can set up Hyper-V to have failover clustering. This is cool since it would allow us to have a high availability environment associated with the virtualized environment. In addition, since Windows Server 2008 R2, Microsoft has added live migration capability in Hyper-V … super cool. Live migration basically allow us to move virtual system (while it is live/running) from one host node to another host node. You don’t have to shut down the virtual system. From the system administrator perspective, this is very helpful, let say that you need to reboot one of the host node (let say after a Microsoft security patch), you can just live migrate the guest system from that host node to another host node in the cluster, and reboot the host node without causing significant downtime to the guest system.

Setup

Recently, we have the opportunity to set up Hyper-V clustering on Dell PowerEdge 710 servers connected to a SAN box using Windows Server 2008 R2 Enterprise Edition. The set up itself went without any problem. We were able to set up a guest (virtual) system and was able to do the live migration back and forth from one host node to another without any problem.

Problem

Everything was running smoothly for a few days, then suddenly one day, the guest system just suddenly dropped from the network. We could not even ping the system. The only way to connect to the system is by using the Hyper-V manager. The guest system itself is up and running, but its network connection just died or hang. We waited to see if it would recover by itself (for about a half day), but it would not. The only way to get the network connection back is to restart the guest system. There are not much clue given by the Windows event log on the guest system, and nothing on the host system. The only error that we are seeing in the Windows event log on the guest system was:

The miniport ‘Microsoft Virtual Machine Bus Network Adapter’ hung.

And

The miniport ‘Microsoft Virtual Machine Bus Network Adapter’ reset.

In term of patches, it seemed that we have downloaded and applied the latest Microsoft patches on both the host and guest systems. We noticed that this problem happened during the time when we copied large sized files (about 500+ GB in total size) from the guest system onto another system in the network. So we tried that again (copying large sized files from the guest system onto another system in the network), and sure enough the network adapter on the guest system would stop working. Strangely, the problem did not appear when we tried copying large sized file from other system in the network onto the guest system. What could it be?

Solution

After searching the Microsoft and Dell sites, we found one Microsoft KB article that seems to provide a hotfix to the problem that we have. It is KB article 974909 (The network connection of a running Hyper-V virtual machine is lost under heavy outgoing network traffic on a Windows Server 2008 R2-based computer). You can find it here. Well what do you know … the title of the KB article actually describe the problem that we were having. So here’s what we did:

• Request the hotfix download from Microsoft
• Download the hotfix
• Backup the guest system
• Shutdown the guest system
• Apply the hotfix on all of the host nodes of the Hyper-V cluster
• Restart the Hyper-V host nodes
• Restart the guest system
• Re-install the Integration service on the guest system

Once those steps were done, we tested the guest system again by copying large sized file from that guest system onto another system in the network. That hotfix seems to have fixed the problem.

One thing that I must say is that before you attempt to re-install the integration service on the guest system, I would strongly recommend that you backup the guest system, one possible way is by creating a snapshot in Hyper-V manager of that guest system. The reason for this is because, when I was trying to re-install the integration service on one of the guest system, I ran into the following error during the install process:

An error has occurred: One of the update processes returned error code 61658.

To resolve this error, we had to do the following:

• Restore the guest system from the snapshot
• While the guest system is off, add the Legacy Network Adapter to the guest system. We did this from the Hyper-V manager.
• Turn on the guest system
• Install the Integration service on the guest system
• Turn off the guest system
• Removed the Legacy Network Adapter
• Turn on the guest system

Lots of steps but somehow that seems to do the trick. We got this solution from Michael Phillip blog post. Since then, the guest (virtual) system has been running great, no more connectivity problem.

Some Notes:

• While researching on this problem, I came across Michael Hanes’ blog in which he listed the hotfixes needed for Windows Server 2008 R2. You might want to check his blog out. It can be found here.
• You might want to also consider calling the Microsoft Support. They might charge you, but they are usually good at troubleshooting this type of problem.

Troubleshooting SQL Server Performance Problem

Probably one of the most inquiries that a Database Administrator get from users are regarding SQL Server performance. As data, number of users, and activities grow, SQL Server performance *might* take a hit. There are many reasons for SQL Server performance problem, such as missing indexes, out of sync query optimization statistics, lack of CPU power or memory, and others. Microsoft actually has come out with great papers on how to troubleshoot SQL Server performance problem. They provide some guidance on how to diagnose the SQL Server performance problem. The white papers are using tools that are readily available such as SQL Server Profiler, System Monitor and DMVs (so no third party is needed).

The white papers:

• For SQL Server 2005, you can find it here.
• For SQL Server 2008, you can find it here.

SQL Table Space Utilization

Working as a database administrator, more often than not, I want to know which are my largest tables. This can be handy to help us to identify tables that might be good candidate for table partitioning. We could also use the information to help determine if data in a particular table might be suitable to be archived.

One stored procedure that can be use to get the detail about space usage of a particular table in SQL Server database is:

sp_spaceused

Detailed information about this stored procedure can be found here.

How to Use the Stored Procedure

Let say that we want to find the space usage for Person.Person table in AdventureWorks database. You can get that information by running the following query:

USE [AdventureWorks];

EXEC sp_spaceused 'Person.Person';

Ok, that's great, but that only return space usage information for one table. What if we want to get space usage information for all tables within one particular database (let say AdventureWorks). To do that, you can run the following query:

USE [AdventureWorks];

CREATE TABLE #TableStats (
 Name NVARCHAR(128),
 Rows CHAR(11),
 Reserved VARCHAR(18),
 Data VARCHAR(18),
 Index_Size VARCHAR(18),
 Unused VARCHAR(18));
 
EXEC sp_MSForEachTable "INSERT INTO #TableStats EXEC sp_spaceused '?'";
 
SELECT Name,
 Rows,
 Reserved,
 Data,
 Index_Size,
 Unused
FROM #TableStats;
 
DROP TABLE #TableStats;

In the query script above, basically we created a temporary table and then run the sp_spaceused stored procedure against each tables within the AdventureWorks database using the sp_MSForEachTable stored procedure. Once that's done, we just query the temporary table and drop the temporary table.

Now, let say that I want to find the top 10 largest table in AdventureWorks database in term of data size. The Data column returned by the sp_spaceused is using VARCHAR data type. So one way to sort it correctly, we can use the following query (basically in the ORDER clause we just need to convert the VARCHAR into INT.):

USE [AdventureWorks];

CREATE TABLE #TableStats (
 Name NVARCHAR(128),
 Rows CHAR(11),
 Reserved VARCHAR(18),
 Data VARCHAR(18),
 Index_Size VARCHAR(18),
 Unused VARCHAR(18));
 
EXEC sp_MSForEachTable "INSERT INTO #TableStats EXEC sp_spaceused '?'";
 
SELECT TOP 10 Name,
 Rows,
 Reserved,
 Data,
 Index_Size,
 Unused
FROM #TableStats
ORDER BY CAST(REPLACE(Data,' KB', '') AS INT) DESC;
 
DROP TABLE #TableStats;

Some Notes:

• As noted in the SQL Server Book on line, if you use sp_spaceused without passing any parameter, you will get the space utilization of the database that you are currently connected to.
• There is a second optional parameter that you can passed onto sp_spaceused, and that's to specify whether or not to update usage statistics (using DBCC UPDATEUSAGE). The parameter accept 'TRUE' or 'FALSE' value, with 'FALSE' set as the default.

Auto Shrink Problem

A few months back, on one of our SQL Server 2005 system, our ETL process would failed and in the application log, we would get the following error:

Error: 1105, Severity: 17, State: 2

Could not allocate space for object ‘[Table_Name]’ in database ‘[Database_Name]’ because the ‘PRIMARY’ filegroup is full.

When seeing this error, my immediate thought was that we might have run out of disk space or the maximum file size that was set on the database Autogrowth setting has been reached. But then when I check those things, those were not the case. We seemed to have more than enough disk space to accommodate the new data, and both the data and log current file size were far below the specified maximum file size.

What could cause this error? So I decided to re-run the ETL process and monitor it. During one of the ETL process, one weird thing that I noticed is that in the middle of the ETL process, the target database data file size suddenly reduced in size, and not long after that, the ETL process would stopped with the above error. Granted that in one of the ETL steps, we truncate data from several of our existing tables, but I was not expecting to see the database data file shrink. Seeing this behavior, I quickly looked at the target database options and sure enough the Auto Shrink option for the database is turned on.

Auto Shrink feature could help to release free space that you have on your database file automatically. The problem is that there is no additional settings that allow you to control on when the Auto Shrink feature should run. In my case, the Auto Shrink shrank the database file during the ETL run and that was not good. So I turned off the Auto Shrink option on the database, and since then I have not gotten the above errors on subsequent ETL runs that we have.

Some Notes:

• Paul Randall (blog | twitter) explains it best on this article in regards to why we should turn off Auto Shrink option from our database.
• Microsoft best practices recommendation is to set the Auto Shrink option off, based on this article.

Patching a Punctured Aerobed

Aerobed is a great invention. It is one of those "great to have" gizmo in the closet. If you have family members/guests coming over for the night, all you need to do is to take the Aerobed out from the closet, plug it into the power outlet, flick a switch ...lo and behold in less than 5 minutes you have an extra bed. It is not as comfortable as a spring bed (although some people might argue that), but it is definitely much more comfortable than sleeping on a sleeping bag, or a couch (in some cases).

Air Leak

One of the worse things that can happen to an Aerobed is a puncture hole. Even the smallest hole can render the Aerobed useless. This actually happened to my Aerobed. Every time I inflate the Aerobed, within a few hours it would sagged so badly that I can not use it comfortably.

Fixing the Leak

Normally, Aerobed comes with a repair kit. But it seemed that I have misplaced mine. So I ended up using:

• Kwik Tek Vinyl Repair Kit (the kit contains a tube of glue like substance and several vinyl patches)
• Eclectic Shoe Goo Clear

As for patching the small hole that was on the Aerobed, I follow the following steps:

• Clean the area around the small hole on the Aerobed
• Apply generous amount of the glue from the Kwik Tek Vinyl Repair Kit to one of the vinyl patch. Then cover the small hole with the vinyl patch.
• Put some weight on the patch and let it sit for about 24 hours. I used the weight of several books. (Notes: After 24 hours, I tried to inflate the Aerobed, however, I still notice some air leak around the edges of the vinyl patch).
• So, I applied generous amount of Eclectic Shoe Goo around the edges of the vinyl patch, and let it sit for another 24 hours.

After I allow the Eclectic Shoe Goo to dry, I inflated the Aerobed and use it for several days. I must say that so far things are looking good. I did not notice any air leak coming from the area of the hole. Also for those several days, the Aerobed stayed inflated and firm.

Some Notes:

• I must say, if the hole on the Aerobed is really small, I think we can fix the Aerobed by just applying a generous portion of Shoe Goo.
• The glue from the Kwik Tek Vinyl Repair Kit and the Shoe Goo have a strong odor, so you might want to use them in well ventilated room.

SharePoint 2010 Usage Blob

A while back I wrote something about the existence of SharePoint usage information in Blob format, and Microsoft actually has a sample code on how to parse the blob. That blog entry can be found here. That’s actually for SharePoint 2007. Actually I was able to utilize that and build a small application that would output the SharePoint usage report. It’s been working pretty well actually.

Now with SharePoint 2010, I was not sure if it still has the usage information in Blob format. I tried to find some documentations online, but could not. So I just give it a try and test it. Happy to report that it seems SharePoint 2010 is still storing usage information in Blob format. The only thing that I am not sure about is whether or not Microsoft will continue to have this.

One thing to note is that one of the new big features that SharePoint 2010 has is the totally redesign usage reports from SharePoint 2007. I must say they look much better that SharePoint 2007 usage reports. In SharePoint 2007, it seems that usage report is an afterthought. But in SharePoint 2010, they seems to really think through the usage report feature well. I would really suggest to give it a looksie.

Related Posts:

• SharePoint Designer
• SharePoint Sample Usage Blob Parser Error

Cleaning Backup and Restore History

Every time you do backup and/or restore on your SQL server, entries are made to several tables in the msdb database. These things can really add up really quickly, especially if you do a lot of backups (like backing up the transaction log every 15 minutes) or restores (hopefully those restores were done on your development servers, instead of production servers). Obviously, this will cause your msdb database to grow over time. In addition, overtime, if you are using backup/restore UI on SQL Server Management Studio, you might notice slower performance when trying to retrieve the backup history.

By default, SQL Server would maintain the backup and restore history indefinitely. It is a good idea to clean up these backup and restore history periodically. There are a couple of ways of doing this.

Stored Procedures

There are a couple of stored procedures in msdb database that can be use to clean the tables in msdb database related to backup and restore.

sp_delete_backuphistory

This stored procedure accepts one parameter, which is the oldest_date (date time data type). This stored procedure would delete backup and restore history that are older than the date time value that you’ve specified on the oldest_date parameter.

EXEC msdb.dbo.sp_delete_backuphistory [@oldest_date =] ‘oldest_date’

For example, let say that you want to delete backup and restore history that are older than April 1, 2010, you can use the following query:

EXEC msdb.dbo.sp_delete_backuphistory ‘4/1/2010’

sp_delete_database_backuphistory

This stored procedure can be use to delete backup and restore history for a given database. It accepts one parameter, which is the database_name.

EXEC msdb.dbo.sp_delete_database_backuphistory [@database_name=] ‘database_name’

For example, let say that you want to delete backup and restore history related to AdventureWorks database, you can use the following query:

EXEC msdb.dbo.sp_delete_database_backuphistory ‘AdventureWorks’

Both stored procedures would delete entries from the following tables:

• backupfile
• backupfilegroup
• restorefile
• restorefilegroup
• restorehistory
• backupset
• backupmediafamily
• backupmediaset
• logmarkhistory (by way of delete trigger on backupset table).

For more information on what these tables are used for, you can go here (http://msdn.microsoft.com/en-us/library/aa260604(SQL.80).aspx).

Maintenance Plans

Alternatively, you can create maintenance plan to delete backup and restore history. All you need to do is to create maintenance plan that include History Cleanup Task. One thing to note is that History Cleanup Task does not only delete the backup and restore history, but also delete SQL job history and maintenance plan log. It executes these three stored procedures:

• sp_delete_backuphistory
• sp_purge_jobhistory
• sp_maintplan_delete_log

Take Away

It is a good idea to clean backup and restore history periodically. You can either create and schedule maintenance plan to run on regular basis or create and schedule SQL job using sp_delete_backuphistory stored procedure.

Backup with Copy_Only Option

Copy_Only option was introduced as one of the backup option starting with SQL Server 2005. It is a pretty useful backup option that I have been using it for a while now. When you back up your database or log with Copy_Only option, it will back up the database or log, and more importantly it will not disturb the backup sequence/routine that you might already have.

How is this useful?

Let say that you have established a routine in which you do a full backup on your database every once a week on Monday morning at 7:00 AM. You also have set up a differential backup to be done once a day at 8:00 PM. Then come Wednesday morning, your application vendor/developer manager is asking for the latest backup of your database to help to troubleshoot an application problem.

What to do?

There are several things that you can do. First you can immediately take a differential backup, and send the Monday’s full database backup and the newly created differential backup to your application vendor/developer manager.

Alternatively, you can also immediately take a full database backup, and send that full database backup to your application vendor/developer manager. However, one thing that you need to do is you need to make sure that you keep that full database backup. It is because the subsequent differential backups will depend on that full database backup that you’ve just take. (I guess a little bit of refresher is in order here. Differential backup contains changes that happen since the last full backup.) It’s just one more thing to worry about.

This is where the Copy_Only option can come in handy. In this scenario, you can use the Copy_Only option when taking the full database backup. The advantage of this is that this will not affect the subsequent differential backups. Those differential backups will still be based on the Monday’s full backup that you have. Let say that on Friday morning, you encountered a data corruption on that database. And you want to restore up to the Thursday differential backup. You can just restore the database from the Monday’s full backup and then restore the Thursday differential backup, and you should be golden.

Demonstration (Without Copy_Only option)

For this demonstration I am using AdventureWorks database. Let say that I have the following backup routine:

• Full Backup #1
• Differential Backup #1
• Differential Backup #2
• Full Backup #2
• Differential Backup #3

I can use the following script:

USE [master];

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NAME = 'Full Backup #1';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_1.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #1'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_2.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #2'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Full_Backup_2.bak'
WITH NAME = 'Full Backup #2';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_3.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #3'

Now, if we try to restore the database from Full Backup #1 and then restore the Differential Backup #3, using the following script:

USE [master];

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Data\AW\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\AW\AdventureWorks_Log.ldf';

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Diff_Backup_3.bak'
WITH RECOVERY;

As expected, we will get the following error:

This differential backup cannot be restored because the database has not been restored to the correct earlier state.

Now, let’s look at why this is happening. Try running the following query:

SELECT name, type, is_copy_only, first_lsn, differential_base_lsn
FROM msdb..backupset
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date ASC

The result is as follow (you might get a different set of result):

name	type	is_copy_only	first_lsn	differential_base_lsn
Full Backup #1	D	0	260000000007500037	NULL
Differential Backup #1	I	0	260000000009700034	260000000007500037
Differential Backup #2	I	0	260000000011200001	260000000007500037
Full Backup #2	D	0	260000000011600037	NULL
Differential Backup #3	I	0	260000000013700034	260000000011600037

You will notice that the Full Backup #1 has first_lsn of 260000000007500037. (LSN is Log Sequence Number). And if you noticed on the differential_base_lsn, the Differential Backup #1 and Differential Backup #2 are also having the same LSN of 260000000007500037. This would indicate that the Differential Backup #1 and Differential Backup #2 are based on Full Backup #1. You will need Full Backup #1 in order to restore from Differential Backup #1 or Differential Backup #2.

Notice also that once you take the Full Backup #2, the subsequent differential back up (Differential Backup #3), has a different base LSN than the Differential Backup #1 or Differential Backup #2. In order to restore from Differential Backup #3, you need Full Backup #2.

Demonstration (With Copy_Only Option)

Let say that I have the following backup routine:

• Full Backup #1
• Differential Backup #1
• Differential Backup #2
• Full Backup #2 (With Copy_Only Option)
• Differential Backup #3

I can use the following script:

USE [master];

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NAME = 'Full Backup #1';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_1.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #1'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_2.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #2'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_2.bak'
WITH NAME = 'Full Backup #2 (With Copy_Only)', COPY_ONLY;

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_3.bak
WITH DIFFERENTIAL, NAME = 'Differential Backup #3'

Now, if we try to restore the database from Full Backup #1 and then restore the Differential Backup #3, using the following script:

USE [master];

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Data\AW\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\AW\AdventureWorks_Log.ldf';

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Diff_Backup_3.bak'
WITH RECOVERY;

It went successfully. So what happen? Let’s try running the following query:

SELECT name, type, is_copy_only, first_lsn, differential_base_lsn
FROM msdb..backupset
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date ASC

Here’s the result:

name	type	is_copy_only	first_lsn	differential_base_lsn
Full Backup #1	D	0	260000000015600037	NULL
Differential Backup #1	I	0	260000000017800034	260000000015600037
Differential Backup #2	I	0	260000000019300001	260000000015600037
Full Backup #2 (With Copy_Only)	D	1	260000000019500001	NULL
Differential Backup #3	I	0	260000000019700001	260000000015600037

As you can see, unlike a regular full database backup, when we use the Copy_Only option, the subsequent differential (Differential Backup #3) is still based on the Full Backup #1. Looking at the differential_base_lsn column, you can see that.

Take Away

You can use Copy_Only option with your backup for that “one off” situation, where you need to take a backup without disturbing your routine backup strategy.

Some Notes

• You can use Copy_Only option when you are doing full database backup or transaction log backup. You can not do it with the differential backup. If you put the Copy_Only option when you do the differential backup, SQL Server will ignore the Copy_Only option.
• You can not use the full backup that you take with the Copy_Only option as the base to restore subsequent differential backup.
• However, you can use the full backup that you take with the Copy_Only option as the base to restore subsequent log backup(s).

Setting Environment Path for Log Parser

Microsoft Log Parser is a nifty little command line tool that can be use to parse and query text-based log files, such as IIS log, windows event viewer, SQL Server error log and others. I’ve mentioned this before, one thing that I like about Microsoft Log Parser is the use of SQL language that we can use to query the log files (I guess I am a tad biased).

After installing Microsoft Log Parser, in order to use it, you need to go to command line application and go to the C:\Program Files\Log Parser 2.2 (Version 2.2 is the latest version of Microsoft Log Parser). If you do it from any other directory, you will get the following message:

‘LogParser.exe’ is not recognized as an internal or external command, operable program or batch file.

To get around this, in order for you to be able to run Log Parser to run from any directory that you have open when using command line, you can set the Log Parser directory as one of the environment path. To do that in Windows Vista system:

• Go to Start menu and then Control Panel.
• In Control Panel, select System and Maintenance.
• Under System and Maintenance, click on System.
• On the left side, there should be Advanced System Settings. Click on that one and the User Account Control window might pop-up. Click on Continue on this.
• On the System Properties window, under the Advanced tab, click on the Environment Variables button.
• On the Environment Variables window, under the System variables section, look and click on Path. Once Path is selected, click on the Edit button.
• You should see a window something like the one below. At this point go to the very end of the variable values field and type in semicolon and the C:\Program Files\Log Parser 2.2.

• Once done, click on the OK button. Then click on the OK button on the Environment Variables window. Then click on the OK button again on the System Properties window.
• If you have any command line open you might want to close them, or start a new one and by now you should be able to use the Log Parser tool from any directory other than the C:\Program Files\Log Parser 2.2 directory.

Related Posts:

• Querying SQL Server Agent Error Log with Microsoft LogParser
• Querying SQL Error Log with Microsoft LogParser

Querying SQL Server Agent Error Log with Microsoft LogParser

Last week I wrote about using Microsoft LogParser to query entries in SQL Error Log. You can read more about it here.

Normally, we would read or query SQL Agent Error Log using SQL Log Viewer. But just as SQL Error Log, SQL Server Agent Error Log is also text-based log. Thus, we can also use Microsoft LogParser to query the log(s).

What is SQL Server Agent Error Log?

SQL Server Agent Error Log contains information, warning or error messages related specifically to SQL Server Agent, information such as when SQL Server Agent service is started or stopped. By default, SQL Server Agent Error Log can be found under the following directory: C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\LOG\. It has file name of SQLAGENT.OUT or SQLAGENT.x.

Using Microsoft LogParser to Read SQL Server Agent Error Log

To output all of the SQL Server Agent Error Logs into a datagrid format, you can use the following command with Microsoft LogParser:

LogParser "SELECT TO_TIMESTAMP(STRCAT(STRCAT(Date,' '), Time), 'yyyy-MM-dd hh:mm:ss') AS DateTime, Message INTO DATAGRID FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT*'" -i:TSV -iSeparator:spaces -headerRow:OFF -iHeaderFile:C:\SQLAgentLogHeader.txt

Some explanations on the option used:

-i:TSV	This is to indicate that the input file used is a TSV (Tab Separated Values) file
-iSeparator:spaces	This is to specify that we use spaces as the separator between values
-iHeaderFile:C:\SQLAgentLogHeader.txt	The SQLAgentLogHeader.txt file can be downloaded from the following location. If you open one of the SQL Server Agent Error Log, you would notice that it does not have any column header. What this option does is that it tell LogParser to use SQLAgentLogHeader.txt as the header format file. We can use the header specify in the header format file in our SQL query.
-i:headerRow:OFF	This just to tell LogParser that the first row of the input file is not header.
-o:DATAGRID	This is to indicate that we want the output into datagrid format

Additional Examples

To query entries in SQL Server Agent Error Log that are older than 1st January 2010, you can use the following command:

LogParser "SELECT TO_TIMESTAMP(STRCAT(STRCAT(Date,' '), Time), 'yyyy-MM-dd hh:mm:ss') AS DateTime, Message INTO DATAGRID FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT*' WHERE DateTime < TO_TIMESTAMP('01/01/2010', 'MM/dd/yyyy')" -i:TSV -iSeparator:spaces -headerRow:OFF -iHeaderFile:C:\SQLAgentLogHeader.txt

To find out when SQL Server Agent has been stopped:

LogParser "SELECT TO_TIMESTAMP(STRCAT(STRCAT(Date,' '), Time), 'yyyy-MM-dd hh:mm:ss') AS DateTime, Message INTO DATAGRID FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT*' WHERE Message LIKE '%SQLSERVERAGENT stopping%'" -i:TSV -iSeparator:spaces -headerRow:OFF -iHeaderFile:C:\SQLAgentLogHeader.txt

Querying SQL Error Log with Microsoft LogParser

What is Microsoft LogParser?

I've been using Microsoft LogParser for a while. It is a great little (yet powerful) tool to query text-based data logs. It is very flexible. It can be use to query comma/tab separated files, IIS logs, Windows Event logs, etc. In term of outputs, we can output the result to datagrid, graph, database, text files, etc. One thing that I love about Microsoft LogParser is that we use SQL expression to query the logs (I am a bit biased on this since I am a DBA).

Microsoft LogParser is a command line utility. The installation file can be located here. There are some people that developed GUI for Microsoft LogParser. One of them can be found here.

Using Microsoft LogParser to Read SQL Error Log

SQL Error Logs in SQL Server provides a lot of information that can be use by DBA to troubleshoot issues with SQL Server. It contains server error messages, backup and restore messages, process messages, etc. We can easily view SQL Error Log using SQL Log Viewer that comes with Microsoft SQL Server Management Studio.

SQL Error Logs by default is stored in the following folder: C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\LOG\. It has file name of either ERRORLOG or ERRORLOG.x. They are text-based logs, and we can use Microsoft LogParser to query the logs.

To output all of SQL Error Logs into a datagrid, you can use the following command with Microsoft LogParser:

LogParser.exe "SELECT TO_TIMESTAMP(STRCAT(STRCAT(DATE,' '), TIME), 'yyyy-MM-dd hh:mm:ss') as DateTime, Source, Message FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG*'" -i:TSV -iSeparator:spaces -iHeaderFile:C:\SQLErrorLogHeader.txt -headerRow:OFF -nskiplines:5 -o:DATAGRID

A bit of explanation of the option used:

-i:TSV	This is to indicate that the input file used is a TSV (Tab Separated Values) file.
-iSeparator:spaces	This is to specify that we use spaces as the separator between values.
-iHeaderFile:C:\SQLErrorLogHeader.txt	The SQLErrorLogHeader.txt file can be downloaded from here. If you open one of the SQL error log, you would notice that it does not have any column header. What this option does is that it tell LogParser to use SQLErrorLogHeader.txt as the header format file. We can use the header specify in the header format file in our SQL query.
-i:headerRow:OFF	This just to tell LogParser that the first row of the input file is not header.
-nskiplines:5	With this option, we skipping the first 5 lines from the input files. If you look at one of the SQL Error log file, the first 5 lines of the error log are just the SQL Server version and build information.
-o:DATAGRID	This is to indicate that we want the output into datagrid format.

Now let's look at the SQL query that we use:

SELECT TO_TIMESTAMP(STRCAT(STRCAT(DATE,' '), TIME), 'yyyy-MM-dd hh:mm:ss') as DateTime, Source, Message FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG*'

Within the select clause, we use the column names specify in the SQLErrorLogHeader.txt. One quick thing to point out here is that, the TO_TIMESTAMP function is being used to convert text into datetime. Within the from clause, we just put the location of the SQL error log files. The neat thing about this is that it accepts wildcard, so LogParser can process multiple files at once.

For the SQL query, you can be as creative as you like. Some examples:

Let say that you want to find out messages in SQL Error Logs that are older than 1st January 2010, you can use the following command:

LogParser.exe "SELECT TO_TIMESTAMP(STRCAT(STRCAT(DATE,' '), TIME), 'yyyy-MM-dd hh:mm:ss') as DateTime, Source, Message FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG*' WHERE DateTime < TO_TIMESTAMP('01/01/2010', 'MM/dd/yyyy')" -i:TSV -iSeparator:spaces -iHeaderFile:C:\SQLErrorLogHeader.txt -headerRow:OFF -nskiplines:5 -o:DATAGRID

Or let say that you are trying to find out about database backups that was done:

LogParser.exe "SELECT TO_TIMESTAMP(STRCAT(STRCAT(DATE,' '), TIME), 'yyyy-MM-dd hh:mm:ss') as DateTime, Source, Message FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG*' WHERE DateTime < Message LIKE '%backup%'" -i:TSV -iSeparator:spaces -iHeaderFile:C:\SQLErrorLogHeader.txt -headerRow:OFF -nskiplines:5 -o:DATAGRID

Fixing User Login Mapping

You might have been in a situation where you have been asked to sync up the development database with production database. Fair enough. So you started to backup your production database and restore that database in the development environment. You thought to yourself, “That was easy.” But then after an hour has past, one of the developer went to you and said that now he can not access the database that you’ve just restored in the development environment. Doh!

What’s Wrong

For SQL Server authentication logins, SQL Server would assign security identifier (SID). SQL Server would store the login information in the master database. Each database would also store information on who can access the database. They are linked together by the user SID. Since you were only restoring the user database, it is very likely that the SID of the users stored in the master database (the one that grants login to the SQL Server) do not match with the SID of the users stored in the user database that you’ve just restored (the one that grants access to the user database). Thus, we have a situation where we have orphan users.

Detecting Orphan Users

To quickly detect orphan users for a particular user database, you can use the following script (in this example assume that AdventureWorks is the user database):

USE [AdventureWorks]
GO
EXEC sp_change_users_login ‘Report’
GO

Alternatively, you can also use the following script:

USE [AdventureWorks]
GO
SELECT [name], [sid]
FROM sysusers
WHERE name not in (‘guest’, ‘dbo’)
AND sid IS NOT NULL
AND issqluser = 1

Fixing Orphan Users

Now let say that when we run the script above, we see that user ‘Developer1’ is orphan. Assuming that in the development SQL Server, we already have login for ‘Developer1’, to fix this, we can run the following script:

USE [AdventureWorks]
GO
EXEC sp_change_users_login ‘Auto_Fix’, ‘Developer1’
GO

Alternatively, we can also run the following script:

USE [AdventureWorks]
GO
ALTER USER Developer1
  WITH LOGIN = Developer1
GO

Finding Stored Procedures, User Defined Functions, and Views to Change

In some cases, we need to make changes to our table structure. One of the challenges that might arise when making changes to table structure is that we need to make sure to review the database objects (stored procedures, views, and user defined functions) that might be affected by the table structure changes that we made. If there is a good documentation in place, we might be able to find the affected database objects pretty easily (this is a good case for having a good design/technical documentation). But since that's not always the case, we need to have a way to find the list of potentially affected database objects.

SQL Management Studio

On our SQL Server Management Studio, we can identify the database objects (stored procedures, views, constraints and user defined functions) associated with a particular table pretty easily. To do that, open SQL Server Management Studio, and on the Object Explorer window, right click on the table that you are going to modify. On the context menu, select View Dependencies. It should open a window that list all the database objects that depend on that particular table.

Stored Procedures/ Functions

There are also stored procedure/functions that you can use to find the object dependencies. If you are still using SQL Server 2005, you can use sp_depends stored procedure. It is very easy to use. For example let say that you want to find the list of database objects that depend on HumanResources.Employee table in AdventureWorks database. You can use the following:

USE [AdventureWorks];   
EXEC sp_depends 'HumanResources.Employee'; 

If you are using SQL Server 2008, you can use the sys.dm_sql_referencing_entities.

Alternatives

For most part, using SQL Management Studio or sp_depends or sys.dm_sql_referencing_entities should be sufficient. However, in situation where we are using dynamic SQL in our stored procedure, we will need a different approach. One such approach would be to do a text search on the database object definition.

Back to the example of finding the list of database objects that depends on HumanResources.Employee table in AdventureWorks database, we can try to use the following script:

USE [AdventureWorks];   
SELECT    B.Name AS ObjectName, B.Type_Desc AS ObjectType    
FROM    sys.sql_modules A    
    INNER JOIN sys.objects B ON A.Object_ID = B.Object_ID    
WHERE    REPLACE(REPLACE(A.[Definition],'[', ''), ']', '') LIKE '%HumanResources.Employee%'; 

The above script has its own limitation. Since it uses wildcard text search, it might return more database objects than it really needs. In our example, it might also returns database objects that contains HumanResources.EmployeeAddress, HumanResources.EmployeeDepartmentHistory, etc.

Another problem that might arise when trying to find object dependencies is that if we use the View Dependencies on SQL Management Studio Object Explorer, or sp_depend, we can only see the object dependencies limited to that one particular database. It could happen that you have a tool database, and within that tool database you have a stored procedure that uses the table that you want to modify. So we need to have a way to search across databases. One way to do that, we can use the following script:

DECLARE @ObjectDependencies TABLE (ObjectName VARCHAR(255), ObjectType VARCHAR(255), DatabaseName VARCHAR(255)) 
  
INSERT INTO @ObjectDependencies   
EXEC sp_msforeachdb 'SELECT B.Name AS ObjectName, B.Type_Desc AS ObjectType, ''?'' AS [Database] FROM ?.sys.sql_modules A INNER JOIN ?.sys.objects B on A.Object_ID = B.Object_ID WHERE REPLACE(REPLACE(A.[Definition],''['', ''''), '']'', '''') LIKE ''%HumanResources.Employee%''' 
  
  SELECT    ObjectName, ObjectType, DatabaseName   
FROM    @ObjectDependencies 

Third Party Tools

There are also some third-party tool that you can use to search for text on database objects (as a way to find object dependencies). One of the tool that I have been using is SQL Digger (http://www.sqldigger.com). It was pretty neat tool that can be use to search text on database object.

SharePoint Designer

If you are working with SharePoint Services or Microsoft Office SharePoint Server (MOSS), chances are that you have been working with SharePoint Designer.

SharePoint Designer can be use to push new pages or contents to SharePoint sites. In addition, we can also obtained SharePoint usage stats from SharePoint Designer. It can also be use to edit HTML, ASP.NET, CSS, XSL, etc. It is not as powerful as Visual Studio, but it should do a pretty decent job. It has a pretty decent What-You-See-Is-What-You-Get (WYSIWYG) editor. In term of its look and feel, SharePoint Designer is pretty similar to its predecessor, Microsoft FrontPage. The best part is that SharePoint Designer has been made available by Microsoft for free. Below are the links to Microsoft site to download SharePoint designer:

SharePoint Designer 2007
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=baa3ad86-bfc1-4bd4-9812-d9e710d44f42

SharePoint Designer 2010 (32-Bit)
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=d88a1505-849b-4587-b854-a7054ee28d66

SharePoint Designer 2010 (64-Bit)
http://www.microsoft.com/downloads/details.aspx?familyid=566D3F55-77A5-4298-BB9C-F55F096B125D&displaylang=en

Some notes:

• SharePoint Designer 2010 RTM (Release to Market) has just come out last month. It is no longer beta version.
• Unlike SharePoint Designer 2007, which only have the 32-bit version, SharePoint Designer 2010 has 32-bit and 64-bit versions.
• According to the SharePoint Designer team blog site, we can have a side by side installation of SharePoint Designer 2007 and SharePoint Designer 2010.
• Also to my understanding, SharePoint Designer 2010 can not be use for MOSS 2007. So you still need to use SharePoint Designer 2007 to manage MOSS 2007.
  

If you are interested to learn more about SharePoint Designer, and also get development news in regards to SharePoint Designer, you might want to check out the Microsoft's SharePoint Designer blog. Microsoft's SharePoint Designer blog can be found under the following URL: http://blogs.msdn.com/sharepointdesigner/.

Last Sunday of the Month

Copy_Only option was introduced as one of the backup option starting with SQL Server 2005. It is a pretty useful backup option that I have been using it for a while now. When you back up your database or log with Copy_Only option, it will back up the database or log, and more importantly it will not disturb the backup sequence/routine that you might already have.

How is this useful?

Let say that you have established a routine in which you do a full backup on your database every once a week on Monday morning at 7:00 AM. You also have set up a differential backup to be done once a day at 8:00 PM. Then come Wednesday morning, your application vendor/developer manager is asking for the latest backup of your database to help to troubleshoot an application problem.

What to do?

There are several things that you can do. First you can immediately take a differential backup, and send the Monday’s full database backup and the newly created differential backup to your application vendor/developer manager.

Alternatively, you can also immediately take a full database backup, and send that full database backup to your application vendor/developer manager. However, one thing that you need to do is you need to make sure that you keep that full database backup. It is because the subsequent differential backups will depend on that full database backup that you’ve just take. (I guess a little bit of refresher is in order here. Differential backup contains changes that happen since the last full backup.) It’s just one more thing to worry about.

This is where the Copy_Only option can come in handy. In this scenario, you can use the Copy_Only option when taking the full database backup. The advantage of this is that this will not affect the subsequent differential backups. Those differential backups will still be based on the Monday’s full backup that you have. Let say that on Friday morning, you encountered a data corruption on that database. And you want to restore up to the Thursday differential backup. You can just restore the database from the Monday’s full backup and then restore the Thursday differential backup, and you should be golden.

Demonstration (Without Copy_Only option)

For this demonstration I am using AdventureWorks database. Let say that I have the following backup routine:

• Full Backup #1
• Differential Backup #1
• Differential Backup #2
• Full Backup #2
• Differential Backup #3

I can use the following script:

USE [master];

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NAME = 'Full Backup #1';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_1.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #1'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_2.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #2'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Full_Backup_2.bak'
WITH NAME = 'Full Backup #2';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_3.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #3'

Now, if we try to restore the database from Full Backup #1 and then restore the Differential Backup #3, using the following script:

USE [master];

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Data\AW\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\AW\AdventureWorks_Log.ldf';

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Diff_Backup_3.bak'
WITH RECOVERY;

As expected, we will get the following error:

This differential backup cannot be restored because the database has not been restored to the correct earlier state.

Now, let’s look at why this is happening. Try running the following query:

SELECT name, type, is_copy_only, first_lsn, differential_base_lsn
FROM msdb..backupset
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date ASC

The result is as follow (you might get a different set of result):

name	type	is_copy_only	first_lsn	differential_base_lsn
Full Backup #1	D	0	260000000007500037	NULL
Differential Backup #1	I	0	260000000009700034	260000000007500037
Differential Backup #2	I	0	260000000011200001	260000000007500037
Full Backup #2	D	0	260000000011600037	NULL
Differential Backup #3	I	0	260000000013700034	260000000011600037

 

You will notice that the Full Backup #1 has first_lsn of 260000000007500037. (LSN is Log Sequence Number). And if you noticed on the differential_base_lsn, the Differential Backup #1 and Differential Backup #2 are also having the same LSN of 260000000007500037. This would indicate that the Differential Backup #1 and Differential Backup #2 are based on Full Backup #1. You will need Full Backup #1 in order to restore from Differential Backup #1 or Differential Backup #2.

Notice also that once you take the Full Backup #2, the subsequent differential back up (Differential Backup #3), has a different base LSN than the Differential Backup #1 or Differential Backup #2. In order to restore from Differential Backup #3, you need Full Backup #2.

Demonstration (With Copy_Only Option)

Let say that I have the following backup routine:

• Full Backup #1
• Differential Backup #1
• Differential Backup #2
• Full Backup #2 (With Copy_Only Option)
• Differential Backup #3

I can use the following script:

USE [master];

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NAME = 'Full Backup #1';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_1.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #1'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_2.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #2'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_2.bak'
WITH NAME = 'Full Backup #2 (With Copy_Only)', COPY_ONLY;

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_3.bak
WITH DIFFERENTIAL, NAME = 'Differential Backup #3'

Now, if we try to restore the database from Full Backup #1 and then restore the Differential Backup #3, using the following script:

USE [master];

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Full_Backup_1.bak'
>WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Data\AW\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\AW\AdventureWorks_Log.ldf';

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Diff_Backup_3.bak'
WITH RECOVERY;

It went successfully. So what happen? Let’s try running the following query:

SELECT name, type, is_copy_only, first_lsn, differential_base_lsn
FROM msdb..backupset
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date ASC

Here’s the result:

name	type	is_copy_only	first_lsn	differential_base_lsn
Full Backup #1	D	0	260000000015600037	NULL
Differential Backup #1	I	0	260000000017800034	260000000015600037
Differential Backup #2	I	0	260000000019300001	260000000015600037
Full Backup #2 (With Copy_Only)	D	1	260000000019500001	NULL
Differential Backup #3	I	0	260000000019700001	260000000015600037

As you can see, unlike a regular full database backup, when we use the Copy_Only option, the subsequent differential (Differential Backup #3) is still based on the Full Backup #1. Looking at the differential_base_lsn column, you can see that.

Take Away

You can use Copy_Only option with your backup for that “one off” situation, where you need to take a backup without disturbing your routine backup strategy.

Some Notes

• You can use Copy_Only option when you are doing full database backup or transaction log backup. You can not do it with the differential backup. If you put the Copy_Only option when you do the differential backup, SQL Server will ignore the Copy_Only option.
• You can not use the full backup that you take with the Copy_Only option as the base to restore subsequent differential backup.
• However, you can use the full backup that you take with the Copy_Only option as the base to restore subsequent log backup(s).

SharePoint Sample Usage Blob Parser Error

SharePoint Usage Report

If you have SharePoint sites, one of the things that you want to know is how users is using the sites. To get that information, you want to analyze the users’ usage. From there you can find out which sites and pages that are popular, users’ usage patterns and trends. You can use those information to improve your SharePoint sites.

There are several ways in which you can get SharePoint sites usage information. The most common one probably through SharePoint Designer. Alternatively, SharePoint also gives us the usage information in a blob format. Blob can be a bit challenging to work with. You will need to parse the blob. But fear not, Microsoft has provided us with a sample usage blob parser. It is Windows SharePoint Services: Usage Blob Parser, which can be found here. The Usage Blob Parser is a simple windows application written in C++.

Compilation Error

After I downloaded the Usage Blob Parser, I tried to open the project with Visual Studio 2008. When I first open the project with Visual Studio 2008, the Conversion Wizard appear. It asked whether I want to convert the project into a Visual Studio 2008 project, which I did and the conversion process went without a hitch.

However, when I tried to build and run the project, I got 2 errors. Both errors are pointing to the same line of code. It is pointing to line 126 of Form1.h file. Here’re the errors that I got:

error C3867: ‘GetUsageBlobSample::Form1::button1_Click’: function call missing argument list; use ‘&GetUsageBlobSample::Form1::button_Click’ to create a pointer to member

error C3350:’System::EventHandler’ : a delegate constructor expects 2 argument(s)

Solution

To fix the errors, here what I did. I opened Form1.h file and go to line 126. On that line, I see the following line of code:

this->button1->Click += new System::EventHandler(this, button1_Click);

All I need to do is to modify it to the following:

this->button1->Click += new System::EventHandler(this, &Form1::button1_Click);

Once I did that, I was able to build and run the project.

Some Additional Notes

• I came across this blog entry by Diego. He has written the SharePoint Blob Parser in C#. It was pretty good. I was able to download the code, made some minor tweaks and use it.

SQL Browser Service

So you have just installed SQL Server 2005/2008 Express. Everything looks good. You then opened SQL Server Configuration Manager. You then looked at the list of SQL Server services installed. You saw SQL Server (SQLEXPESS) service. You quickly thought, “That must be the database engine service. It’s up and running, cool.”

Then you also noticed SQL Browser service. Then you thought to yourself, “Hmm… what’s that? Should that service be running?”.

So, What is SQL Browser Service?

SQL Browser service was introduced in SQL Server 2005. In short, it is a service that provides information about SQL Server instances available on that particular SQL Server to clients. In addition, it also listen to client requests coming to SQL Server. For more information, you might want to read the following reference from SQL Server Book Online: http://msdn.microsoft.com/en-us/library/ms181087.aspx.

Do We Need to Have SQL Browser Service Running?

Well it depends. If you have a named instance of SQL Server (by default, SQL Server 2005/2008 Express is installed as a named instance), and the SQL Browser Service is not running, you might run into some problem connecting to the named instance remotely. For the sake of illustration, let say that you have installed SQL Server 2005 Express, as a named instance of SQLEXPRESS on one of your server named FOOBAR. You then open SQL Server Management Studio on another workstation, and you try to connect to the SQLEXPRESS instance that you’ve just installed. When you are presented with the Connect to Server window, you typed in FOOBAR/SQLEXPRESS. Now if you don’t have SQL Browser Service running, you will most likely get the following error:

Cannot connect to FOOBAR\SQLEXPRESS
Additional information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)

If you have your SQL Browser Service running, you will not get this error. In order to start SQL Browser service, you can do it through the SQL Server Configuration Manager.

Some Additional Notes

• You can actually connect to your name instance SQL Server remotely without having to start running SQL Browser server. To do this, you need to specify the port number that the named instance listen to. You can get the port number from SQL Server Configuration Manager. If you are trying to connect using SQL Server Management Studio, to connect to a named instance on a specific port number, you need to use the following syntax on the server name: [Server Name]/[Instance Name],[Port number]. For illustration, if you have FOOBAR as your server name, SQLEXPRESS as your instance name and 1234 as the named instance port, you would type in the following on the server field: FOOBAR/SQLEXPRESS,1234 (Notes: Remember to use comma instead of colon).
• You don’t need to have SQL Browser service running if you are connecting using Shared Memory connection (locally).

SQL Server Remote Connection

The Situation

Ok, so you’ve installed SQL Server 2005/2008 on one of your development system. The installation went as smooth as you could hope for. Everything seems to be working perfectly. You are able to connect using SQL Server Management Studio locally and created a user database. You then decided to try to spice thing up a notch. You want to try to connect to your newly created user database that is on your newly installed SQL Server from another workstation within your Local Area Network (LAN).

To your surprise, you are unable to connect to the newly created user database. Gosh darn, what went wrong? You have checked that you are using the correct login credential with the appropriate permission. You also have checked your firewall setting, and even turned off the Windows Firewall that you have. But still, you could not make that connection. When you try to connect to the SQL Server using SQL Server Management Studio on the remote workstation, you got the following error message:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connection. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server).

Possible Solutions

One possible explanation for the problem that you have is that the newly installed SQL Server is only allowing local connection. Depending on what SQL Server edition that you’ve installed, by default SQL Server Express, Evaluations, and Developer edition is set to only allow local connection. But don’t worry, you can change this. There are two alternatives in which you can enable remote connection. They are:

Use SQL Server Surface Area Configuration

Notes: This only works for SQL Server 2005. There is no SQL Server Surface Area Configuration in SQL Server 2008. To allow remote connection, you can follow the following steps:

• Go to SQL Server 2005 Surface Area Configuration (It should be located on All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration)
• Once SQL Server 2005 Surface Area Configuration window is open, on the left navigation, there should be Remote Connection. Click on that option and you should see a screen similar to below:

• To enable remote connection, you need to choose Local and remote connections. You then will need to choose which protocol you are going to allow for the connection, either TCP/IP, named pipes or both.
• Once you make your choice, click on the Apply button and then Ok button.
• You then might want to restart your SQL Server service. Once it has been restarted, you should be able to connect from a remote workstation.

Use SQL Server Configuration Manager

You can use SQL Server Configuration Manager to enable remote connection in SQL Server 2005 and SQL Server 2008. To allow remote connection, you can follow the following steps:

• Go to SQL Server Configuration Manager (It should be located on All Programs > Microsoft SQL Server 2005 (or Microsoft SQL Server 2008) > Configuration Tools > SQL Server Configuration Manager).
• Once SQL Server Configuration Manager is open, go to Network Configuration, and then Protocols for [SQL Server Instance Name]. It should now display a screen similar to below:

• Here, depending on your network configuration, you might want to enable the appropriate network protocol. So if you want to enable TCP/IP protocol, you can do a right click on TCP/IP and select Enable.
• You then might want to restart your SQL Server service. Once it has been restarted, you should be able to connect from a remote workstation.

Some Additional Notes

• For SQL Server 2005, if you made your changes on SQL Server Surface Area Configuration, your changes will be automatically reflected on SQL Server Configuration Manager, and vice versa.
• If you are installing SQL Server as named instance (which is most likely case for SQL Server Express installation), you might want to make sure that your SQL Browser Service is running. If SQL Browser Service is not running, You will need to specify port number to connect to the SQL Server.
• Notice that by default Shared Memory protocol is enabled by default. This protocol is being used when you are connecting locally.

About Me

My name is Lucas Kartawidjaja. Currently I am working as a Microsoft SQL Server Database Administrator (DBA) for a global company. I have been working as a DBA since 2002. I have hands-on experience in database design, programming, and administration. In term of certifications, I am a Microsoft Certified Database Administrator (MCDBA) for SQL Server 2000 and Microsoft Certified Technology Specialist (MCTS) for SQL Server 2008. In addition to working as a DBA, I also have experience working as System Administrator and Microsoft.NET Developer. When I am not working, I enjoy fishing and playing ice hockey.