Download the PowerShell Pack from code.msdn.microsoft.com/PowerShellPack - more than 650 scripts and functions divided into a number of PowerShell modules.

Windows 7 Resource Kit PowerShell Pack Modules:

WPK Create rich UIs quickly and easily from Windows PowerShell

TaskScheduler List, create or delete scheduled tasks

FileSystem Monitor files and folders, check for duplicate files and disk space

IsePack Shortcuts and add-ons to the PowerShell Integrated Scripting Environment

DotNet Explore loaded types, find commands that can work with a type, and discover how to work with .NET and COM objects in PowerShell

PSImageTools
Convert, rotate, scale, and crop images and get image metadata

PSRSS
Scripts and functions for working with the FeedStore provider

PSSystemTools
Get OS and hardware information

PSUserTools
Get the users on a system, check for elevation, and start a process as an administrator

PSCodeGen
Generate PowerShell scripts, C# code and P/Invoke

To use any of the modules, you first need to import it.

reg add “HKLMSOFTWAREMicrosoftWindows NTCurrentVersionConsoleTrueTypeFont” /v 00 /d Consolas

I recently encountered this wired error with database mirroring in SQL Server 2005. I have a big database (>1TB) with two servers doing mirroring for high availability. DBServer1 is the Principal and DBServer2 is the Mirror. They both have SQL Server 2005 Standard 64-bit Version with Service Pack 3 (9.0.4226) installed on Windows Server 2003 R2 x64 with Service Pack 2. All the hardware and software configurations are exactly the same. They are using High safety without automatic failover (synchronous) mirroring operating mode. So I have to do manual failover when needed. I did the failover for server maintenance a couple times and it worked very well. Recently, since there are a few big tables in this database which doesn’t change any more, I made the filegroup which contains only these tables to read-only in order to save the backup space. But here it came the problem. When I did a manual failover from DBServer1 to DBServer2, DBServer2 resumed the Principal successfully, but DBServer1 as Mirror showed “Synchronizing” not “Synchronized”. When I launched the Database Mirroring Monitor, it showed trouble with this mirroring. So I checked SQL Server Log, it was filled up with these error messages trying to sync with principal:

Error: 823, Severity: 24, State: 3.
>
> The operating system returned error 5(Access is denied.) to SQL Server during a write at offset 0000000000000000 in file ‘D:MSSQLDATAbigfile.ndf’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
>
>

I realized this might be something related with the read-only filegroup. So I did some testing on my test server with a small database which also has read-only filegroup. It had the same errors when doing a manual failover. I did some research on Microsoft KB and found out this KB973897 for SQL Server 2008 which is exactly the same problem. It says “The fix for this issue was first released in Cumulative Update 4 for SQL Server 2008 Service Pack 1”. But it doesn’t mention this problem applies to SQL Server 2005. And I didn’t find anything about the fix for SQL Server 2005. So right now my solution is: before doing the failover, make all read-only filegroups to readwrite, perform manual failover, then turn them back to read-only after the failover. Kind of pains, but hope some solution out…

Just spot this article from windows blog:

How to Build Your Own Windows Home Server

Have fun!

SQL 2000 tells the license you have. But SQL 2005 no longer tracks licensing. So when running ServerProperty(‘LicenseType’), it always returns ‘DISABLED’. Pretty much like ‘I don’t know’ or ‘I don’t care’. It is by design. So if you still want to use this to keep track of licensing of hundreds or thousands SQL servers in stead of paper licenses, you have to do some tricks in registry. Here is the blog post about this which is not in SQL BOL.

Following are excerpted from that blog post:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90MSSQLLicenseInfoMSSQL9.00

To configure SQL Server for Per Processor, add these Registry Values under that Key adjusting for the number of processors you have a license for:

Mode REG_DWORD 2 _LICENSE_MODEPERPROC
ConcurrentLimit REG_DWORD 4 Number of Processors licensed.

To configure SQL Server for Per Seat licensing, add these Registry values under the Key adjusting for the number of seat license you have.

Mode REG_DWORD 0 _LICENSE_MODEPERSEAT
ConcurrentLimit REG_DWORD 100 Number of client licenses registered for SQL Server.

I’m using Logitech webcam to do the video conferencing with my colleagues. But it’s causing the problems whenever I tried to install/uninstall SQL Server 2008/2008 R2. The system check kept showing me “Need to restart”. So I restarted my box, but it still kept asking me restart my computer. It drove me nuts. Finally after a few Bing and found this bad guy. Just clean out anything in the following registry key, and my box is in peace and happily works with SQL Server 2008.

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession ManagerPendingFileRenameOperations

This morning I need to remote desktop to a server’s console session to do something. I haven’t used this option for quite a while. So like I always did, I type “mstsc /console” in my powershell command windows. But surprised me, it was not connecting to server’s console session. So I tried second time, it was still not working like before. Then I realized my OS has upgraded to Windows 2008 R2 from Windows 2003. So I ran “mstsc /?” to check if any changes on the remote desktop client. And it surprised me again. “/console” parameter is not there any more. It is changed to “/admin”. Even the description doesn’t say “console session”, but I think the “session for administering” may just refer to “console session”.

Following is the parameter and description of MSTSC under Windows Server 2003/ Windows XP

mstsc [] [/v:] [/console] [/f[ullscreen]] [/w:] [/h:] [/public] | [/span] [/edit “connection file”] [/migrate] [/?]

/console Connects you to the session logged on at the console of a server running Windows Server 2003 or a server on which the Terminal Server role service is installed.

and under Windows Vista/Windows 7/Windows 2008/Windows 2008 R2:

mstsc [] [/v:[:]] [/admin] [/f] [/w: /h:] [/public] [/span][/edit “connection file”] [/migrate] [/?]

/admin Connects you to a session for administering the server.

Here you can find a good reference about this command.

A training machine has default instance of SQL 2005 standard with SP2 on it. After installing a new named instance on it, I tried to apply Service Pack 3 to both the default instance and the named instance. SP3 was successfully applied to the named instance, but failed on the default instance with following error: “Product: Microsoft SQL Server 2005 - Update ‘Service Pack 3 for SQL Server Database Services 2005 ENU (KB955706)’ could not be installed. Error code 1603.”

I did some search and found this solution http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic23830.aspx.

Following are excerpted from the link:

1. Remove the following registry subkeys that store SID settings:

_HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.XSetupSQLGroup
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.XSetupAGTGroup
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.XSetupFTSGroup
HKEY_LOCALMACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.XSetupASGroup

Note In these registry subkeys, MSSQL.X is a placeholder for the corresponding value on a specific system. You can determine MSSQL.X on a specific system by examining the value of the MSSQLSERVER registry entry under the following registry subkey:

_HKEY_LOCALMACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance NamesSQL

2. Reinstall the SQL Server 2005 service pack or the SQL Server 2005 hotfix package.

Recently, I came across a need to show an SSIS package execution progress in client .net program. The package is putting the progress info in a log table. So basically client just need to check that log record to get the information. At the beginning, I was using a Timer control in the program to run a SQL query every 10 seconds to get the progress. But because some steps in SSIS package may take less than 10 seconds, and some take more than a few minutes, the program may skip some steps’ update in log table, and can not show every step’s progress to users. So I need a way to monitor the update of that log table. Whenever the log record is updated, I need to show it to the users. The solution I got is SQL Notification Services and SqlDependency.

SQL Notification Services is based on the new features of SQL Server 2005: Service Broker.aspx) and Query Notification.

First, we have to enable Service Broker for our database. Use this query to check Service Broker for database:

SELECT is_broker_enabled FROM sys.databases WHERE name LIKE N’myDB’
>
>

If returning 1, then means it is enabled. Otherwise, we have to run following query to enable it.

ALTER DATABASE myDB SET ENABLE_BROKER
>
>

Then write some codes in client program using ADO.NET 2.0 SqlDependency to get notified whenever log table gets updated. Here is the detail document for how to use Query Notifications. In System.Data.SqlClient namespace: SqlDependency class provides the simplest way to use but without much of customizations. And SqlNotificationRequest provides you more options of customizations. In ASP.NET 2.0, there is a SqlCacheDependency helper class, too. You can find a good sample here. And here has some codes if you want to use SqlDependency with LINQ.

In case of losing SA password and builtinAdministrators is not in sysadmin role, you can restart SQL 2005 in single-user mode (maintenance mode) to add other accounts to sysadmin role to recover it. The detail is in this blog post.