SQL 2008 : Cluster DTC Configuration

SQL 2008 : Cluster DTC Configuration

In order to configure a DTC per SQL instance perform the following steps:

Create a new DTC resource

i.            Look for a green arrow in the middle pane next to “Configure a Service or Application” and click on it to open the “High Availability Wizard”

ii.            Choose “Next” to go to the “Select Service or Application” screen

iii.            Choose “Distributed Transaction Coordinator (DTC)” then click “Next”

iv.            You can either use the default name or create your own unique name for the “Client Access Point” then click “Next”. You may want to name it something like DTC_YourSQLServerResourceName. For example: if your SQL instance’s virtual name is SQL01, call your DTC resource something like DTC_SQL01 or MSDTC-SQL Server (SQL01).

v.            Choose a disk that is not already used and will not be the disk you choose during SQL Server setup.

vi.                  Click “Next” twice.

 

Install SQL Server on at least one node.

 

Move DTC to the SQL Server group

i.            Right click on the DTC resource and choose “More Actions” then “Move this resource to another service or application”.

ii.            Choose your SQL Server group in “Select A Service or Application”.

iii.            Right click on the DTC resource and choose “Properties”. Go to the “Policies” tab and uncheck “if restart is unsuccessful, fail over all resources in this service or application” for the DTC resource unless you are sure you want a DTC failure to cause SQL Server to fail over. This is the same thing as “affect the group” in older versions of Windows.

iv.            Optionally you can take DTC offline and make it dependent on the SQL Server name and disk. Then remove its dependencies on the original name and disk. Then you can optionally delete the original IP, name, and disk or move them out of the SQL Server group.

(Optional for SQL Server 2008) Map SQL Server to this DTC resource.

If you skip this step SQL Server 2005 will use the default DTC. SQL Server 2005 will only use the DTC in the SQL Server group if that one is the default for the entire cluster or if you do the mapping step. SQL Server 2008 will use the DTC in its group unless that DTC is not online/started when SQL Server starts or if you create a mapping to a different DTC.

i.                     Open a dos prompt using “Run as Administrator”.

ii.                   Modify the below statement with a mapping name (make up a name to id the mapping), service (the SQL Server service name – not the display name), and ClusterResourceName (DTC resource name) then run it
Example: Msdtc -tmMappingSet -name DTC_SQL01_Mapping -service “MSSQL$SQL01” -ClusterResourceName “MSDTC-SQL Server (SQL01)”  <<-- Note the correction I made here on 3/21/09 to the service name for SQL, and the additional verification steps below
Do you want to continue with this operation? [y/n] y [enter]

iii.                 Run “msdtc -tmmappingview *” to verify the mapping was created successfully. The mapping is also stored in HKEY_LOCAL_MACHINE\Cluster\MSDTC\TMMapping\Service\ as a new key named the same as the mapping parameter used in the msdtc command.

iv.            To verify SQL Server is mapped to the expected instance of DTC you can either

§ Shut down all the other DTC services and make sure the mapped DTC is online. Verify you can do a BEGIN DISTRIBUTED TRANSACTION from this SQL Server.

§ Do a BEGIN DISTRIBUTED TRANSACTION from this SQL Server, verify it succeeds and roll it back. Then shut down the mapped DTC and verify a new BEGIN DISTRIBUTED TRANSACTION will work.

e)      Enable network access

i.                 Run DcomCnfg

ii.               Navigate to Computers.Component Services.My Computer.Distributed Transaction Coordinator.Clustered DTCs

iii.              Right click on the DTC service for this SQL Server group and select “Properties”.

iv.               Go to the security tab and check “Network DTC Access”, “Allow Inbound”, and “Allow Outbound”.

v.                When you hit “Apply” or “OK” it will restart the DTC resource.

AD DS : PDCe Time Server Configuration

AD DS : PDCe Time Server Configuration

Use the following script to configure your PDCe to sync its time using an external time source:

w32tm /config /manualpeerlist:”tock.usno.navy.mil,0x9 tick.usno.navy.mil,0xa” /syncfromflags:MANUAL
w32tm /config /update
w32tm /resync

This will perform all of the required registry changes under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Parameters without having to actually modify the registry.

Check the system event log for Event ID 35, W32Time ‘The time service is now synchronizing the system time with the time source tock.usno.navy.mil.’ entries once complete.

Exchange 2007 : Managing Shared Mailboxes

Exchange 2007 : Managing Resource Mailboxes

To create a shared mailbox:

New-Mailbox -database “MAILSERVER\MDB1\Mailbox Database” -Name Cancel -OrganizationalUnit “domain.local/Exchange Objects/Resource Mailboxes” -DisplayName “user1” -UserPrincipalName “user1@domain.local” -Shared

To set the country code on the user (may be used for address list policies):

Set-User Cancel -CountryOrRegion “GB”

Grant full access/sendas permissions:

Add-MailboxPermission Cancel -User “user2” -AccessRights FullAccess
Add-ADPermission Cancel -User “user2” -Extendedrights “Send As”

BackupExec : 0xE0008524 Unable to initialize the snapshot

BackupExec : 0xE0008524 Unable to initialize the snapshot

This error is generally caused by not running the BackupExec service as a local administrator account (which is a good thing!).

V-79-57344-34110 – AOFO: Initialization failure on: “System?State”. Advanced Open File Option used: Microsoft Volume Shadow Copy Service (VSS).
Snapshot provider error (0xE0008524): Unable to initialize the snapshot because the Advanced Open File Option (AOFO) is not installed on the target computer. You must install this option, restart the computer, and then run the job again.
Check the Windows Event Viewer for details.

To resolve this issue:

Configured ‘full controll’ permissions on the DACL for the service account on the following registry key:

   HKEY_LOCAL_MACHINE\SOFTWARE\VERITAS\Backup Exec\Engine\Misc

SQL : Performing Point-in-Time Recovery

SQL : Performing Point-in-Time Recovery

Pre-requisites:

  • Requires full recovery model on database
  • Requires a full backup
  • Requires Transaction Log backups to be configured (at a frequency of your choice)

The following TSQL will enable you to perform point-in-time recovery on an SQL database:

/* SQL Point-in-time recovery script */
/* Uses the last full back, then TRN files from the last backup. */

/* Restore the last Full backup */
RESTORE DATABASE [POTRecovery]
FROM DISK = N’D:\MSSQL$POTRecovery\Data\Backup\POTRecovery.bak’
WITH FILE = 1, NOUNLOAD , STATS = 10, NORECOVERY

/* Restore the TRN backups, using the same point-in-time for each */
/* Date format is YYYY-MM-DD HH:MM:SS – 24Hr clock */

RESTORE LOG [POTRecovery] FROM DISK = N’D:\MSSQL$POTRecovery\Data\Backup\POTRecovery_tlog_201003051015.TRN’
WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , STOPAT = N’2010-03-05 10:20:00

RESTORE LOG [POTRecovery] FROM DISK = N’D:\MSSQL$POTRecovery\Data\Backup\POTRecovery_tlog_201003051016.TRN’
WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , STOPAT = N’2010-03-05 10:20:00

RESTORE LOG [POTRecovery] FROM DISK = N’D:\MSSQL$POTRecovery\Data\Backup\POTRecovery_tlog_201003051024.TRN’
WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , STOPAT = N’2010-03-05 10:20:00

/* Now set the database into a useable mode – without this the database will be stuck in a ‘Loading’ state */
RESTORE DATABASE  [POTRecovery] WITH RECOVERY

Using a third party backup tool and SQL-based TRN backups you can still recover as above, just ensure you perform the database restore using Data Protector then use SQL Management Tools to perform the point-in-time recovery.

Windows : Reset WBEM Repository

Windows : Resolving WMI Connectivity Issues

VPN Clients

First check whetherCheckpoitn Secure Client is installed; the integrated Firewall within this VPN client can cause chaos with WMI. Ensure this is updgraded to version ‘VPN-1 SecureClient NGX R60 HFA03 with support for Windows 7 – CP_SecuRemoteSecureClient_NGX_R60_HFA3’.

WBEMTEST

Next try connecting to the amchine using wbemtest; from a command line on any Windows XP + system execute the command wbemtest, this will open a new window;

    1. Click Connect, then modiy the name space to be \\client_name\root\cimv2, then click connect.

Windows Firewall

If this fails confirm the following ports/settings are enabled in Windows Firewall (if configured):

    1. TCP Port 135
    2. Remote Administraton

You can always reset the Windows Firewall using the command netsh reset firewall

Reset WBEM Repository

To reset the WBEM repository follow the following instructions:

    1. Use the Start menu to right-click My Computer.
    2. Press the Manage item.
    3. Double-click Services and Applications in the left-hand pane.
    4. Press Services to expand it.
    5. Scroll to Windows Management Instrumentation in the right-hand pane and  right-click it.
    6. Press Stop.
    7. Use Windows Explorer to delete all the files in the %SystemRoot%\System32\Wbem\Repository folder.
    8. Shutdown and restart your computer. The Windows Management
    9. Instrumentation service will start and the files will be re-created

Verify Registry Settings

If WMI is still not working verify the following registry keys:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Rpc\Internet]
“Ports”=hex(7):37,00,30,00,30,00,30,00,2d,00,37,00,31,00,30,00,30,00,00,00,00,\
  00
“PortsInternetAvailable”=”Y”
“UseInternetPorts”=”Y”

Active Directory : Shadow Groups

Active Directory : Shadow Groups

If you’ve ever wanted to base user group membership on a container within Active Directory, i.e. Organisation Unit’s, you’ll know this is not possible. Using a simple script you can create ‘Shadow Groups’ and even automate the update of their membership to reflect changes in the Active Directory structure.

First create a new group, in this case ‘UK Computer Objects‘.

Next execute the following commands, changing the OU containging the security group as highlighted in green, and the seach scope for the objects you wish to add to the group in question as highlighted in red. The -chmbr option clears the membership of the group and re-writes it, therefore be careful if there are any existing members!

dsquery computer -limit 0 “OU=Sites,OU=UK,DC=domain,DC=local” | dsmod group “CN=UK Computer Objects,OU=Shadow Groups,DC=domain,DC=local” -chrmbr

If you need to add additional objects to the group without wiping it membership use the -addmbr option:

dsquery computer -limit 0 “OU=Administrators,OU=UK,DC=domain,DC=local” | dsmod group “CN=UK Computer Objects,OU=Shadow Groups,DC=domain,DC=local” -addmbr

This script can be sceduled to run froma  server on a daily or weekly basis, ensure it is executed as a user with sufficient AD DS permissions to write membership to the group object.

Exchange 2007 : Recipient Flter Multiple Countries

Exchange 2007 : Recipient Flter Multiple Countries

Here is an example recipient filter used in an Email Address policy that will apply an address based upon multiple factors, including one of two different countries:

Set-EmailAddressPolicy “NL Users” –RecipientFilter {((Co -eq “Netherlands, The” -or Co -eq “Netherlands”) -and (RecipientType -eq ‘UserMailbox’))} –EnabledEmailAddressTemplates ‘SMTP:[email protected]’, ‘smtp:[email protected]

Exchange 2007 : Delegate User DL Management via Exchange Shell

Exchange 2007 : Delegate User DL Management via Exchange Shell

The following command will grant a user the rights to modify the members of a Distribution List:

Add-ADPermission -Identity:’DL Display Name’ -User:sAMAccountName -AccessRights ReadProperty, WriteProperty -Properties ‘Member’