SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Replication (2005)
 mirror , replicate or else...?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

clement.store
Starting Member

China
25 Posts

Posted - 07/03/2010 :  00:28:26  Show Profile  Reply with Quote
Hello there,

A - SQL server 2005 standard sp2 ( production for system A - preferably left intact)
B - SQL server 2005/2008 R2 standard. ( for replicate /Mirror / .... from A )

I have sql servers as the above.

Here's my situation:
My difficuluty is that I dont want to intefere with A but i need to syn data from A to B in order to
create some dynamic views in B based on A for some reporting. ANd I need to keep A intact as much as possible except configuring for sync.

Would u have any clue as for which means would be best for my scenario ?

Thanks a lot!
Clement

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 07/03/2010 :  03:29:02  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Also asked here: http://www.sqlservercentral.com/Forums/Topic947146-291-1.aspx

--
Gail Shaw
SQL Server MVP
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 07/03/2010 :  18:03:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
Sounds like you should use transactional replication. Mirroring isn't a solution for you since you need to use B for reporting.

Could you provide some more details as to what B needs to do? "create some dynamic views in B based on A for some reporting" isn't enough information.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

clement.store
Starting Member

China
25 Posts

Posted - 07/04/2010 :  05:14:03  Show Profile  Reply with Quote
I m actually having a business object server 3.1 on B. And I wilL create some dynamic views from A since A is having new tables anytime. I have learnt that mirroring is not a viable solution. And I wonder if I shud use oneway replication A->B since dynamic views only exist in B. I m still googling a way to find an easy way to implement transactional replication. I have some stored procdure for creating these views on B( executed when there is new table in A). There will also be some master data fed from SSIS. Pls hint me on an easy way to achieving it. Thank you.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 07/06/2010 :  01:50:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
How current does the data need to be in B?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

clement.store
Starting Member

China
25 Posts

Posted - 07/07/2010 :  02:13:58  Show Profile  Reply with Quote
The data can be like 15 minutes lag.

Thank you very much for all your time. I have finally created another database in the same instance and access the source db using the db.owner.table notation. This way, i kept all the sp or views in the new DB. ANd I can keep the source DB intact.
Yet I have learnt a lot about replication and mirroring in these forums. Thank you to SQLteam.com and sql server central that I have benefited so much from your experiences.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 07/07/2010 :  11:38:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
In the setup you just mentioned, you could interfere with A greatly if you are using B for reporting and referencing it via the three-part naming convention.

With this setup, you might as well just use synonyms instead.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

clement.store
Starting Member

China
25 Posts

Posted - 07/07/2010 :  21:35:50  Show Profile  Reply with Quote
Thanks for the suggestion and I will probably use synonyms for coding.
Go to Top of Page

clement.store
Starting Member

China
25 Posts

Posted - 07/08/2010 :  04:38:45  Show Profile  Reply with Quote

But I wonder if there's any mechanism built inside SQL server that when I m reading data from a table while other programs are trying to write things on the same table at the same moment. Would there a conflict...? Cus the read and write applications are from different programs. So Does it mean i have to implement a table lock when executing reporting operation on A ..?

thanks again.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 07/08/2010 :  12:07:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes there can be a conflict, unless you switch to read_committed_snapshot isolation level at the database level. With the default isolation level, reads can block writes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000