Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Having a live backup database

Hi Experts,

I have a VB application that writes to an SQL database.  The customer wants the application to write to a second database on a different server as a backup that can be used immediatly if server 1 is down.  Before I start modifying my application to save to both databases instead of one, I was wondering if there might be a better way.

Is there some functionnality in SQL Server to keep an up to date copy of my database easily ?  Any suggestions ?

Thanks !
0
jmantha709
Asked:
jmantha709
  • 4
  • 2
  • 2
  • +3
4 Solutions
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
If you're using SQL server and NOT MSDE/SQL Express, then you can use Replication to keep the data in sync.  There are also other ways of doing this and let me say that I've never heard of an application that writes to TWO SQL servers at the same time.  This is something that is, in my experience, ALWAYS handled by the SQL server - they could cluster the SQL servers, for example.
0
 
HuyBDCommented:
0
 
jmantha709Author Commented:
Ok, never used replication so I'll have a look at it to try to sort it out and I'll get back to you guys tommorow

Thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jmantha709Author Commented:
I created a snapshot publication and a push subscription, everything seems to be running and active but I don't get the updated/apended records in my backup database...

What am I missing ?

This is the SQL Script to create my publication :

- Enabling the replication database
use master
GO

exec sp_replicationdboption @dbname = N'GESDOC', @optname = N'publish', @value = N'true'
GO

use [GESDOC]
GO

-- Adding the snapshot publication
exec sp_addpublication @publication = N'GESDOC', @restricted = N'false', @sync_method = N'native', @repl_freq = N'snapshot', @description = N'Snapshot publication of GESDOC database from Publisher JOE.', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @retention = 0, @allow_queued_tran = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_dts = N'false', @allow_subscription_copy = N'false', @add_to_active_directory = N'false'
exec sp_addpublication_snapshot @publication = N'GESDOC',@frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 224500, @active_end_time_of_day = 0, @snapshot_job_name = N'JOE-GESDOC-GESDOC-2'
GO

exec sp_grant_publication_access @publication = N'GESDOC', @login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'GESDOC', @login = N'distributor_admin'
GO
exec sp_grant_publication_access @publication = N'GESDOC', @login = N'JOE\JMantha'
GO
exec sp_grant_publication_access @publication = N'GESDOC', @login = N'sa'
GO

-- Adding the snapshot articles
exec sp_addarticle @publication = N'GESDOC', @article = N'APPCOLUMNS', @source_owner = N'dbo', @source_object = N'APPCOLUMNS', @destination_table = N'APPCOLUMNS', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'Application', @source_owner = N'dbo', @source_object = N'Application', @destination_table = N'Application', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'ApplicationUtilisateur', @source_owner = N'dbo', @source_object = N'ApplicationUtilisateur', @destination_table = N'ApplicationUtilisateur', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'ApplicationUtilisateurGroupe', @source_owner = N'dbo', @source_object = N'ApplicationUtilisateurGroupe', @destination_table = N'ApplicationUtilisateurGroupe', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'BASKET_TABLE', @source_owner = N'dbo', @source_object = N'BASKET_TABLE', @destination_table = N'BASKET_TABLE', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'Batchqueue', @source_owner = N'dbo', @source_object = N'Batchqueue', @destination_table = N'Batchqueue', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'DOC_ID1', @source_owner = N'dbo', @source_object = N'DOC_ID1', @destination_table = N'DOC_ID1', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'Groupe', @source_owner = N'dbo', @source_object = N'Groupe', @destination_table = N'Groupe', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'GroupQueue', @source_owner = N'dbo', @source_object = N'GroupQueue', @destination_table = N'GroupQueue', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'INDEX_QUEUE', @source_owner = N'dbo', @source_object = N'INDEX_QUEUE', @destination_table = N'INDEX_QUEUE', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'LOG_TABLE', @source_owner = N'dbo', @source_object = N'LOG_TABLE', @destination_table = N'LOG_TABLE', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'MAILBOXES', @source_owner = N'dbo', @source_object = N'MAILBOXES', @destination_table = N'MAILBOXES', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'NiveauSecuriteGroupe', @source_owner = N'dbo', @source_object = N'NiveauSecuriteGroupe', @destination_table = N'NiveauSecuriteGroupe', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'tblLog', @source_owner = N'dbo', @source_object = N'tblLog', @destination_table = N'tblLog', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'tblLogArchive', @source_owner = N'dbo', @source_object = N'tblLogArchive', @destination_table = N'tblLogArchive', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'TESR', @source_owner = N'dbo', @source_object = N'TESR', @destination_table = N'TESR', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'TESTJOE', @source_owner = N'dbo', @source_object = N'TESTJOE', @destination_table = N'TESTJOE', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'UNIQUE_NO_TABLE', @source_owner = N'dbo', @source_object = N'UNIQUE_NO_TABLE', @destination_table = N'UNIQUE_NO_TABLE', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'GESDOC', @article = N'Utilisateur', @source_owner = N'dbo', @source_object = N'Utilisateur', @destination_table = N'Utilisateur', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO

-- Adding the snapshot subscription
exec sp_addsubscription @publication = N'GESDOC', @article = N'all', @subscriber = N'JOE\MSGPSBM', @destination_db = N'GESBACKUP', @sync_type = N'automatic', @update_mode = N'read only', @offloadagent = 0, @dts_package_location = N'distributor'
GO

0
 
mastooCommented:
Log shipping would also be an option.
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
Are you sure - log shipping doesn't keep it instantly up-to-date.  Replication CAN keep things up to date to, at least to the second.
0
 
mastooCommented:
No, you're right it is not instant or transactional with updates.
0
 
LowfatspreadCommented:
neither log shipping or replication can "keep" you up todate in realtime...

transactional replication should keep your subscribing database closely in step, dependant on the
resources you choose to invest ... Network speed , additional servers (ie do you have a spearate distribution server?) etc..

in any Fail-over case however , you'll need to consider the architectural issues involved in switching to the backup database and the effect of completed but not reported transactions in the databases...  

 
0
 
jmantha709Author Commented:
So any idea what might be wrong with my replication then ?
0
 
LowfatspreadCommented:
i think you just set up a snapshot publisher....

what you need to do is setup transactional replication between your main and backup databases...

the transactional replication starts off with a snapshot to populate all the tables and then sends the database
changes as the transaction complete to the backup database..
0
 
imran_fastCommented:
You did set Snapshot replication

user immidiate transactional replication two way (you might set it one way) if both server are online always and are connected to one another
else use
qued replication
other method is use
merge replication if both server
======================

here are the step to set merge replication it will add one additional column to track changes for further information check for books online

exec sp_replicationdboption @dbname = N'GESDOC', @optname = N'publish', @value = N'true'
GO

use [GESDOC]
GO

-- Adding the merge publication
exec sp_addmergepublication @publication = N'GESDOC', @description = N'Merge publication ', @retention = 14, @sync_mode = N'native', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @centralized_conflicts = N'true', @dynamic_filters = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @conflict_retention = 14, @keep_partition_changes = N'false', @allow_subscription_copy = N'false', @allow_synctoalternate = N'false', @add_to_active_directory = N'false', @max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @publication = N'GESDOC',@frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 1, @frequency_subday_interval = 5, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 500, @active_end_time_of_day = 235959, @snapshot_job_name = N'JOE-GESDOC-GESDOC-2'
GO

exec sp_grant_publication_access @publication = N'GESDOC', @login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'GESDOC', @login = N'distributor_admin'
GO
exec sp_grant_publication_access @publication = N'GESDOC', @login = N'JOE\JMantha'
GO
exec sp_grant_publication_access @publication = N'GESDOC', @login = N'sa'
GO

-- Adding the merge articles

exec sp_addmergearticle @publication = N'GESDOC', @article = N'APPCOLUMNS', @source_owner = N'dbo', @source_object = N'APPCOLUMNS', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'truncate', @creation_script = null, @schema_option = 0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null, @vertical_partition = N'false', @destination_owner = N'dbo', @auto_identity_range = N'false', @verify_resolver_signature = 0, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'GESDOC', @article = N'Application', @source_owner = N'dbo', @source_object = N'Application', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'truncate', @creation_script = null, @schema_option = 0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null, @vertical_partition = N'false', @destination_owner = N'dbo', @auto_identity_range = N'false', @verify_resolver_signature = 0, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'GESDOC', @article = N'ApplicationUtilisateur', @source_owner = N'dbo', @source_object = N'ApplicationUtilisateur', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'truncate', @creation_script = null, @schema_option = 0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null, @vertical_partition = N'false', @destination_owner = N'dbo', @auto_identity_range = N'false', @verify_resolver_signature = 0, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'GESDOC', @article = N'ApplicationUtilisateurGroupe', @source_owner = N'dbo', @source_object = N'ApplicationUtilisateurGroupe', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'truncate', @creation_script = null, @schema_option = 0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null, @vertical_partition = N'false', @destination_owner = N'dbo', @auto_identity_range = N'false', @verify_resolver_signature = 0, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'GESDOC', @article = N'BASKET_TABLE', @source_owner = N'dbo', @source_object = N'BASKET_TABLE', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'truncate', @creation_script = null, @schema_option = 0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null, @vertical_partition = N'false', @destination_owner = N'dbo', @auto_identity_range = N'false', @verify_resolver_signature = 0, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0
GO
so on for all tables
-- Adding the merge subscription


exec sp_addmergesubscription @publication = N'GESDOC', @subscriber = N'JOE\MSGPSBM', @subscriber_db = N'GESBACKUP', @subscription_type = N'push', @subscriber_type = N'local', @subscription_priority = 0.000000, @sync_type = N'automatic', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @enabled_for_syncmgr = N'false', @offloadagent = 0, @use_interactive_resolver = N'false'
GO
0
 
jmantha709Author Commented:
Thanks, for the info.  I didn't forget this, just got sidetracked for a few days...

I'll give a look at this this week and keep you posted.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now