How to Synchronize a Reporting Server

Have you ever had to build a reporting system where you wanted to off-load the reporting function from the main production server? It's a problem that can be solved with a variety of methods, for example:

- Database Mirroring: Works very nicely in an asynchronous environment but has the downside of only duplicating the data to a single server (so, no high-availability options) plus Enterprise Edition is needed.

- Replication: Has the benefit of duplicating data to multiple servers (plus, other benefits like filtering some transactions while letting others go through) and runs well on Standard Edition, but is harder to setup and maintain.

- Log Shipping: Has the benefit of duplicating data to multiple servers for high availability and runs on Standard Edition, but it has big drawbacks due to needing to apply the logs to the reporting server (such as difficulties with operations like rebuilding indexes).

However, SQL Server MVP Simon Sabin, has some good recommendations. He recommends that you stick with replication over log shipping. Why?

In Simon's own words, "Because with log shipping you need down time to restore logs in a log ship model to manage the restore i.e kicking users off. Also, impacted greatly by any operations like index rebuilds on the primary server. There are also orphaned login issues (as with all restored based mechanisms)."

Simon went on, "With transactional replication, you don't have to replicate all your data. You can have additional tables in your reporting database You can include extra indexes in your reporting system to optimise for reporting while you cannot with log shipping. You can include extra stored procedures and views that aren't in your main database. You don't have to have all the SPs, views etc that are in the main database. You can amend the replication SPs to provide an TL (Transform Load) mechanism, i.e. add history, timestamps not in the main database."

On the other hand, "Transactional replication requires careful management with schema changes. Bulk DML statements need to be managed on the main database to avoid excessive replication transactions. For us having the DB always available and being able to make reporting specific optimisations are the key points. We already have a transactional replication model and so this is knowledge we already possess."

Installing IIS After Visual Studio 2005

If you happen to install IIS after Visual Studio 2005, you must first reregister IIS by running aspnet_regiis -i from the 2.0 version of the framework folder. Optionally, you might have to repair the dotnet framework.

:\wcu\dotNetFramework\dotnetfx.exe /t:c:\temp /c:"msiexec.exe/fvecms c:\temp\netfx.msi"

Even after this, you might still not be able to create an ASP.NET website within IIS. When you look in the Location pulldown, you'll see File System, FTP, but no HTTP. To fix this we looked into IIS to ensure that the ASP.NET tab of the default website was bound to ASP.NET 2.0 instead of 1.1.

在Windows 2003上設定MSDTC

MSDTC on Windows 2003 SP1的注意事項

Windows 2003在安全上的管控較Windows 2000嚴格，因此，如果Web或SQL被安裝在Windows 2003時，要注意預設的安裝選項並不支援跨機器的分散式交易，記得要去手調一下:

而Windows 2003 SP1就更機車了，在MSDTC上會多了幾個選項，當然預設又是最嚴的選項—Mutual Authentication Required，它是個未來才會生效的選項，現在的效果等同於Incoming Caller Authentication Required，而且只有在兩台DTC都是Windows XP SP2或Windows 2003才適用。如果Web或SQL其中一台的OS是Windows 2000時，No Authentication Required是唯一的選擇。

最後，還有一點要記得，在XP SP2/Windows 2003 SP1，如果你有啟用LAN網卡上的防火牆，記得要將MSDTC加入例外清單，用UI設或用以指令都可以:
netsh firewall set allowedprogram %windir%\system32\msdtc.exe MSDTC enable
如何排除MSDTC的網路問題(主要使用DTCPing，另外，也有提到用Ghost或VM Disk Image複製多台Server時的CID重覆問題)
http://support.microsoft.com/default.aspx?scid=kb;zh-tw;306843
Windows 2003 SP1上的MSDTC新選項說明
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/html/2627a956-

當Client與SQL Server分處於不同AD Domain時，可能導致分散式交易失敗。現象是Connection.Open()傳回錯誤訊息: New transaction cannot enlist in the specified transaction coordinator.

問題肇因於MSDTC靠雙向的RPC管道溝通，因此SQL Server所在的主機也要有能力連回Client端，跨Domain的遇到的狀況多半是因DNS不同台導致機器名稱無法解析，因此請確定SQL Server與Client”彼此相識”! 最簡單的測試方法是開個DOS視窗，用ping serverMachineName, ping clientMachineName確認雙方可以用機器名稱解析到對方，當然用DTCPing測試也行，還可以一併測試Firewall等Issue。

如 果發現某一方無法解析機器名稱時，最簡單的方法是在windows/system32/drivers/etc/lmhosts(這個檔案預設是不存在 的，但可以將lmhosts.sam更名為lmhosts，直接拿來用)裡加上一列如192.168.1.1 myClient的宣告，再下個nbtstat –R，之後ping測試如果OK，問題應該就可解決囉!