June 12, 2008 at 3:44 am
Hi,
We have a database that resides on a server at one of our internet service providers (the server has the same domain information as our internal domain but does not reside on our domain, it's stand alone). The server runs SQL Server 2005 Enterprise Edition (incl. SSIS, SSAS and SSRS), this single instance of SQL satisfied all business units (Application and Reporting). We would like to "replicate" the database locally for reporting purposes.
I have been doing some investigation and it seems that "Log Shipping" would be the ideal solution. The only problem is that the two SQL Servers will not be able to see each other, thus the transaction log would need to be uploaded/downloaded using FTP. Is this possible?
June 12, 2008 at 7:30 am
Yes. It is possible. You will not be able to configure it with the log shipping tools included with SQL Server, but it can be done. I think the Red-Gate tool would help with this.
Log shipping is really just restoring a full database to another server in NORECOVERY or STANDBY mode and then repeatedly applying transaction log files to this database to keep it updated. I have used it with FedEx transferring the log files for me. You have to take some care with your DR backups to make sure you do not end up with LSN's that are not in sequence (forcing you to restore a full backup). I would suggest you do some research to get yourself familiar with Log Shipping, but it should work fine for what you are doing.
June 13, 2008 at 6:52 am
Agreed, I don't think you can use the built-in tools for Log Shipping (the secondary server instance and database must be visible to the primary database).
You probably need to do Custom Log Shipping. There are various solutions, and code freely available (probably on this site if you hunt around). For the FTP, you should be able to create an SSIS package to grab the latest log files from an FTP server. Alternatively, a simple DOS batch file kicked off every hour would suffice.
I'll add a proviso though: in the book "Pro SQL Server 2005 High Availability", the author strongly recommends against using a log-shipped database for reporting. I would recommend you buy the book if you really want to understand this area.
Andy
June 14, 2008 at 4:38 pm
Question: Why not save yourself a lot of trouble and simply replicate the database tables you need from your ISP to your office SQL server?
- In our shop, we setup the main SQL server as a distribution server and a replication publisher. We then created a one-way transactional publication for the database "articles" that we needed, followed by a subscription to this publication by our office SQL Server.
This automates the approach and gives you near real-time synchronization between the two SQL servers.
June 16, 2008 at 3:48 am
Transactional Replication could be a more suitable solution to provide a reporting-only database.
However, I think in this case TCP Ports will be a big issue. It's already mentioned that the 2 MSSQL Servers cannot see each other. Does Replication work in this case? I had a quick search but I couldn't find a definition for the ports that Transactional Replication requires. But I would hazard a guess that the Subscriber must be able to communicate directly (eg. TCP/1433 and 1434) with the Distributor.
Personally, I dislike Replication due to the awkward way it has to be configured, and the limitations it places on the database schema, row size, ntext datatype, etc.
June 16, 2008 at 8:50 pm
- I believe most of your objections refer to earlier versions of SQL Server.
- Please review the following brief discussion in Redmond Developer New of the Replication implementation in SQL 2005.
URL: http://reddevnews.com/features/article.aspx?editorialsid=607
June 17, 2008 at 2:44 am
I did have a proviso in my post that my main production experience with Replication is with MSSQL 2000, but I deleted it for the sake of brevity. However, I'm still not a fan of Replication, and I have played with it in MSSQL 2005.
The article you reference is nice, but hardly insightful and critical (I don't mean negative, I mean unbiased research).
For example, all the comments on Identity Management were quite possible to achieve in MSSQL 2000.
Anyone considering rolling out Replication in a production environment would script the whole process, from end to end. So all the bells and whistles added to the GUI are a waste of time.
Has anyone else noticed that Microsoft, and companies closely associated with Microsoft, are very happy to openly slate their products, once a new version has been released? Before MSSQL 2005 hit the shelves, you won't find a single bad comment from Microsoft about MSSQL 2000. But once the shiny new version is around, somehow it is ok to disown the older version.
Statements such as "error reporting has also been significantly enhanced" just make me laugh. The significant enhancement has been to rewrite the error messages. Who wrote the original error messages? Oh, that only happened in the old version....
Ok, rant over, time for coffee. I'm just wondering how long it will be until I hear from Microsoft that MSSQL 2005 really wasn't up to the job, but the shiny new version is just what I need....
Andy
June 17, 2008 at 11:31 am
My experience is:
- We deployed replication on our clustered 64-bit SQL 2005 nodes.
- We used the built-in GUI to configure transactional replication without incident.
- This is a production environment.
- Replication latency has been essentially non-existent.
- Automated alerts notify us should a latency issue arise.
- My legs are kicked-up on my desk. Yawn - time for a nap.
June 18, 2008 at 2:13 am
And when was the last time you checked the conflict tables for any problems? Are you sure all of your Identity columns are incrementing correctly on all Subscribers? How do you handle updates to Views and Stored Procedures? If you haven't scripted out the process for setting up your Replication, what do you do in the event of a problem (yes, even Clustered Nodes fail)?
I'm not fundamentally against Replication, but to suggest it is straightforward and problem-free is greatly over-simplifying things.
Andy
June 18, 2008 at 8:08 am
Well, it's clear that we have different opinions on this. Because this solution works in our shop, I'm not inclined to revert to more tedious technical solutions, but thanks for the tips.
Can't wait to start testing the SQL 2008 builtin solutions.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply