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 !
LVL 15
jmantha709Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.