| Author |
Topic |
|
Hannibal
Starting Member
20 Posts |
 Posted - 2002-06-04 : 18:27:21
|
| Here's the situation.I need to copy data from a production server to a standby server. This standby server will be used for failover (manual) and for data mining. The servers will not be in the same location. I have done a bit of research today to find the best solution (other than a clustered environment, which will probably be implemented in the future) and have come up with two choices:- Transactional Replication- Log ShippingBased on the information I found, data mining wouldn't be possible with Log Shipping, leaving me with Transactional Replication. Can you correct me if this is wrong?Now, security. If I implement TR, what is my best method to secure the data transfer? VPN? SQL Server Encryption? Any advice in this area would be greatly appreciated.And just to make sure, is transactional replication my best option, or did I overlook something?Thanks for your help! |
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-06-04 : 18:54:09
|
| Transactional replication is good for maintaining a copy of the data for reporting/analysis purposes, but certainly isn't meant for maintaning a standby server. (because, transactional replication only transfers data changes and some of the database objects, from the published database. And there's more to be considered for a standby server, than just a published database.)For reporting purposes, I would use transactional replication.For disaster recovery/standby purposes, I would use backup restore or log shipping.--HTH,Vyashttp://vyaskn.tripod.com |
 |
|
|
Hannibal
Starting Member
20 Posts |
Posted - 2002-06-04 : 19:22:22
|
| Thanks for your input. You mentioned using TR isn't recommended for a standby server, because there of more requirements than just a published database. Can you elborate on this, or send me to an article? I was under the impression that should the main server fail, a manual process could be put in place to change the client software to point to the 'standby' server and everything would work fine until the main server was fixed/replaced. Of course this isn't an ideal solution compared to auto failover, but it's cheap and fairly easy to implement from what I've read. Am I missing something? |
|
|
|
Hannibal
Starting Member
20 Posts |
Posted - 2002-06-04 : 23:18:36
|
| I was thinking about this some more tonight and wanted to ask if it's acceptable to put a standby database (using log shipping) and a query database (using transactional replication) on the same physical server but within two separate databases?Although, I am still interested in knowing more about why the transactional replication isn't used for a standby and/or query server. :) |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-06-05 : 05:27:26
|
| To maintain a proper standby server, that can replace the main production server when needed, there are a lot of things that need to be on the standby server, that aren't transferred by replication.For example, replication only transfers tables and few other objects. Very limited support for schema changes. If you add new tables or new objects, you need to manually add them to the publication, otherwise the standby database won't receive those additions. Managing replication in this case becomes cumbersome (adding new articles and synching them). It is okay to use replication for standby purposes on a fairly static database, but not on a very dynamic database.If you are looking for exact copies of the database on the standby server, backup/restore (log shipping) is the best way to do it.I think it is okay to have both logshipping and replication for different requirements, provided your servers can handle the load.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
Hannibal
Starting Member
20 Posts |
Posted - 2002-06-05 : 09:29:16
|
| Thanks for the great info, Vyas! |
|
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2002-06-05 : 10:15:05
|
| In setting up a hot-standby server, I can tell you from experience that DTS is probably your best and easiest route. Some of the biggest gotchas still and probably always will be your login accounts and making sure that they are synched on both servers. Once this is done:Example uses one Database:1. Backup and Restore Database from Prod to standby using the WITH STANDBY(Will make it read-only works only for 2K to 2K restores) otherwise use the NORECOVERY option (This will allow you to restore and wait for additional Trans Logs in a 7-2K scenario useful for an upgrade for moving prod onto a 2K server)2. Using DTS with 2 Connections (Prod & Standby), 1 Dynamic Properties Task (2K) to actually manipulate what the SQL Text will be. and then 4 Execute SQL Tasks. 2 Tasks will be nothing more than defining what the filenames will be for the Backup And Restore statements that have the filenames dynamically put in. Ex:(this execute sql task defines what the output parameter FN will be which will be.)select 'Backup Transaction PRD to disk = ' + char(39) + '\\sapqasdb\l$\Backup\PRD' + cast(datepart(dd, getdate()) as varchar)+ cast(datepart(hh, getdate()) as varchar) + cast(datepart(mi, getdate()) as varchar) + '.bak' + char(39) + ' With Init' as FNThe other two tasks will be the receivers of the information from the first two Execute statements and they will both actually be performing the backups and restores with the precedence on success.I've used this method for about 25 upgrades without any problems and when the server switch occured all I had to do was rename the server then execute sp_dropserver, sp_addserver.Hope this helps.JamesH. |
|
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2002-06-05 : 10:18:14
|
| Sorry if the last post was confusing, it's hard to type a lengthy response in this little space. I forgot to mention that your last restore should be with the RECOVERY option specified which will bring it out of read-only mode and ready for use.JamesH. |
|
|
|
Hannibal
Starting Member
20 Posts |
Posted - 2002-06-05 : 14:20:33
|
| James, I read an article that sounded similar to what you posted, but it was with log shipping instead of DTS. I am aware of the problems associated with synching the logins, but the database in question is pretty static in that respect (and I remember reading that using W2K Authentication removes this problem anyway). Are there any other reasons I might consider DTS over log shipping? I've used DTS in the past, but I seem to remember programming a method to tell if the data had changed so I wasn't sending everything each time, but maybe I'm mistaken. |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-06-05 : 14:45:57
|
| IMHO, for disaster recovery and minimal data loss, BAckup-Restore/Logshipping is the way to go. DTS is only good for "Transferring and transforming data between SQL Servers and/or other data sources". It's very difficult to get DTS to transfer only the changed data. Also, how can one do point-in-time recovery or recovery to-the-point-of-failure with DTS?--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
Hannibal
Starting Member
20 Posts |
Posted - 2002-06-05 : 15:22:39
|
| Vyas,Can you expand on the differences between backup restore and log shipping? I've read some places that use them as if they were interchangable terms, but I haven't found a definite description of the differences. |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-06-05 : 15:26:05
|
| Hannibal, Log shipping is nothing but an automated process of backup-restore. Log shipping continuously backs up the transaction log of the primary database and restores those backups onto the standby database at regular intervals.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
Hannibal
Starting Member
20 Posts |
Posted - 2002-06-05 : 15:35:19
|
| Ahhh, that makes sense now!! Thank you. I'm starting to like these forums. ;) |
|
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2002-06-05 : 15:47:46
|
| vyas: If you really think that "DTS is only good for Transferring and transforming data between SQL Servers and/or other data sources"then you should really look into it further. With DTS I can mirror what BizTalk is trying to do. I assure you that although DTS is great at transforming data it is much better at many other things.In the scenario that I provided to Hannibal, in the event of failure you would do a final transaction log backup (WITH NO_TRUNCATE) and restore it either manually or through the DTS (WITH RECOVERY OPTION). To be quite honest, and I doubt you can prove me wrong, there is really no difference between what I propose and Log Shipping; furthermore, I would venture to say that my method gives the DBA more Control over his own destiny...IMHO, JamesH. |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-06-05 : 17:32:56
|
| James, I do understand that a lot can be done with DTS apart from transfer and transformation, especially with the support for ActiveX scripting, a dozen tasks and more. But DTS is good at transfering and transforming data, and the rest can be done in any other language like VB or VC.In my previous post, I haven't actually had a look at your post. But now I did, and what you are doing is nothing but "log shipping". It is just that you are using DTS to do the backups and restores, and the built-in logshipping will make use of jobs and stored procedures to do the same.So, backup/restore is still the best option for maintaining standby servers.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2002-06-06 : 12:09:14
|
| Not the best way, the Only way... I think you just mis-understood my approach and why I prefer (yes it's just preference) not to use Log Shipping just yet.JamesH. |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-06-06 : 12:50:06
|
| >> Not the best way, the Only wayDefinitely not the "Only way" if you are ready to shell out money on high-end hardware based solutions.Again, I would like to point out that, you are indeed doing "log shipping" and using DTS to facilitate logshipping. I even did logshipping in 6.5, when that term was not around. It's nothing but backing and restoring transaction logs.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
|
Transactional Replication VS Others?