This question came up today: how can I change the IP address between the principal and mirror in database mirroring?

Not a common operation but this procedure worked in an isolated lab environment where we had full control over the application and transaction activity.  We wanted to introduce a WAN latency injector so needed to change the database mirroring IP addresses on the principal and mirror. 

1. Stop application activity
2. Remove mirroring (SET PARTNER OFF)
3. Stop Mirroring endpoints (on principal and mirror)
4. Alter Mirroring endpoints to use new IP addresses e.g. ALTER ENDPOINT SET LISTENER_IP =
5. Start endpoints on principal and mirror
6. Enable mirroring (ALTER DATABASE <dbname> SET PARTNER = TCP://x.x.x.x)

I’ll try and find the exact scripts we used and upload them here.

I haven’t had a chance to look through these yet so I can’t comment on the content but I thought I would post here to share these new resources.

HP Business Intelligence Sizer for Microsoft SQL Server 2005/2008

http://h71019.www7.hp.com/ActiveAnswers/us/en/sizers/microsoft-sql-bi.html

HP Whitepapers on SQL Server 2008 Data Warehousing / Business Intelligence

http://h20195.www2.hp.com/V2/GetPDF.aspx/4AA2-5263ENW.pdf

http://h20195.www2.hp.com/V2/getdocument.aspx?docname=4AA2-8173ENW.pdf

http://h20195.www2.hp.com/V2/GetPDF.aspx/4AA2-7162ENW.pdf

I often see questions about transactional replication performance problems, especially around latency/delays between the publisher and subscriber(s) so I’ve put a few pointers below on what to investigate.  Latency between the publisher, distributor and subscriber(s) is, more often than not, the symptom of other causes for example, poor I/O capacity on subscribers, blocking/locking, hotspots on indexes, high number of virtual log files etc.

Troubleshooting tips:

• Look at perfmon counters (disk reads and writes/sec, avg disk/sec read and avg disk/sec write) to ensure there is enough capacity and that the latency on the data and log drives are within our recommended boundaries. 
• Look at waitstats (use DMVstats – highly recommended) to see what resources are waiting. This will give you a good indication where the bottleneck is. 
• Look at the transactional replication performance monitor counters (pending Xacts, transactions/sec, latency etc.)
• Check the number of VLF’s http://support.microsoft.com/kb/949523 as this can have a negative impact on log scanning if there are a very high number of VLF’s, I tend to ensure this value is below 1000.
• Use tracer tokens to check latency from publisher to distributor to subscriber
• Use agent logging to external files, -outputverboselevel 2 –output <dir\file> to troubleshoot data issues
• Look in mslogreader_history, msdistribution_history & msrepl_errors in distribution database
• Consider external factors e.g. consult the network/SAN specialists to check external issues such as network bandwidth/array performance issues etc.
• If blocking is suspected then use the blocked process trace definition.  I can highly recommend this as it provides incredibly valuable information about the blocked and blocking processes. 
• If you are using database mirroring in conjunction with transaction replication then the log reader may be have to wait for the record to be hardened on the mirror.  This can be avoided by using trace flag 1448 on the publisher.

Optimisation tips:

• Use agent profiles to optimise for workloads
• Implement Read Committed Snapshot Isolation (RCSI) on subscribers to alleviate reader/writer blocking (when doing this consider the impact on tempdb as this is where the version store is located)
• Ensure the distribution history clean-up job is correctly trimming the distribution database tables.
• If there are data consistency issues, consider using tablediff to compare data in publisher/subscriber tables (warning: this may take a while with large volumes of data) however tablediff can in fact be used against a subset of the data using views.
• Be careful about using –skiperrors to bypass consistency errors http://support.microsoft.com/kb/327817
• Consider using –SubscriptionStreams on the distribution agent to use multiple threads to apply the data to the subscribers, read this http://support.microsoft.com/kb/956600 and this http://support.microsoft.com/kb/953199
• If initialising from a backup/copy of the database, don’t enforce integrity on the subscribers.  Drop the constraints or use the NOTFORREPLICATION option. 

We recently had the opportunity to test a couple of the Fusion IO PCI-Express 640GB SSD cards http://www.fusionio.com/Products.aspx in a Dell R900 server, unfortunately time was against us and we were unable to do this. The Fusion IO SSD cards would dramatically increase the IOPS capacity and personally, I think they would be suited to storing tempdb.  I’m a bit cautious about using SSD for data and transaction log so tempdb seems like the best solution.