There are ten DB Servers in live network. Out of 10 DB servers, one of them
has the database and the contents are replicated throughout. The size of
the database is about 20 GB and it is to be updated on weekly basis. The
time frame for replication is limited to 2 hours and all the data must be
updated from the Update DB server within 2 hours given.
What is the best data replication method on this scenario?
Jason,
transactional would be the fastest. Log shipping might be of alternative
interest. I'm assuming that the subscribers don't need to update the data.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label replicated. Show all posts
Showing posts with label replicated. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Best practices for reporting: Replicated servers + data warehouse server?
Good afternoon,
Lately there has been discussion of what the best practice would be for
enterprise reporting needs. Specifically, we currently have online OLTP
servers for our business apps, as well as a data warehouse server.
A member of our team has suggested that we create a new server farm (or
server) for real time reporting to alleviate the real time reporting
burden off the production servers.
I was looking for any advice or pointers to design a topology that can
support our current real time systems as well as data warehousing
needs, while still minimizing the burden of reporting against our live
production servers.
The mention of replication has come to mind, but I'm not completely
sold on attempting to replicate our production data for the "heck of
it"...
Any thoughts?
Best regards,
-Sean
Can you specify requirements for the reporting data? What would be the
maximum latency involved? I assume that reporting from dw isnt good enough
beacause of the latency...
MC
"Sean Aitken" <sean.aitken@.gmail.com> wrote in message
news:1139858184.012942.18310@.g14g2000cwa.googlegro ups.com...
> Good afternoon,
> Lately there has been discussion of what the best practice would be for
> enterprise reporting needs. Specifically, we currently have online OLTP
> servers for our business apps, as well as a data warehouse server.
> A member of our team has suggested that we create a new server farm (or
> server) for real time reporting to alleviate the real time reporting
> burden off the production servers.
> I was looking for any advice or pointers to design a topology that can
> support our current real time systems as well as data warehousing
> needs, while still minimizing the burden of reporting against our live
> production servers.
> The mention of replication has come to mind, but I'm not completely
> sold on attempting to replicate our production data for the "heck of
> it"...
> Any thoughts?
> Best regards,
> -Sean
>
|||Good morning MC,
There aren't any real specific requirements at this time. But, we do
have the need to ensure that real time reporting doesn't impact
production systems. Apparently, there are some applications that have
caused problems against performance of real time systems with some
reports.
I'm more of less just looking for some existing topology patterns that
have been proven to satisfy the needs of the business. Our organization
has about 2000 employees all over the world, and we have many systems
in place, SQL Server as well as Oracle.
My personal feeling is that any real time reporting be designed with
performance in mind and anything else should pull from the data
warehouse, with the granularity as designed into the reporting and
model requirements.
I wish I had a good case example, but my main motivation for
approaching this issue is that another developer on our team made a
proposal to actuall replicate the entire prod environment for all real
time reporting. I'm having a hard time buying into that idea.
Thanks for any insight!
Cheers!
-Sean
|||Hi Sean,
sure.....there are some useful books to read on my beginners page.
http://www.peternolan.com/Beginners/...0/Default.aspx
The best book on architecture is the Corporate Information Factory book
by Bill et al.
http://www.amazon.com/exec/obidos/AS...700697-6194236
Though for some reason Amazons links are not working at the moment.
CIF is a well defined and well articulated architecture for designers
to take into consideration when building end to end Information
Infrastructure for sizable companies. Well worth reading...
Peter
|||Thank you very much Peter!
I'll be checking them out today!
Cheers!
-Sean
Lately there has been discussion of what the best practice would be for
enterprise reporting needs. Specifically, we currently have online OLTP
servers for our business apps, as well as a data warehouse server.
A member of our team has suggested that we create a new server farm (or
server) for real time reporting to alleviate the real time reporting
burden off the production servers.
I was looking for any advice or pointers to design a topology that can
support our current real time systems as well as data warehousing
needs, while still minimizing the burden of reporting against our live
production servers.
The mention of replication has come to mind, but I'm not completely
sold on attempting to replicate our production data for the "heck of
it"...
Any thoughts?
Best regards,
-Sean
Can you specify requirements for the reporting data? What would be the
maximum latency involved? I assume that reporting from dw isnt good enough
beacause of the latency...
MC
"Sean Aitken" <sean.aitken@.gmail.com> wrote in message
news:1139858184.012942.18310@.g14g2000cwa.googlegro ups.com...
> Good afternoon,
> Lately there has been discussion of what the best practice would be for
> enterprise reporting needs. Specifically, we currently have online OLTP
> servers for our business apps, as well as a data warehouse server.
> A member of our team has suggested that we create a new server farm (or
> server) for real time reporting to alleviate the real time reporting
> burden off the production servers.
> I was looking for any advice or pointers to design a topology that can
> support our current real time systems as well as data warehousing
> needs, while still minimizing the burden of reporting against our live
> production servers.
> The mention of replication has come to mind, but I'm not completely
> sold on attempting to replicate our production data for the "heck of
> it"...
> Any thoughts?
> Best regards,
> -Sean
>
|||Good morning MC,
There aren't any real specific requirements at this time. But, we do
have the need to ensure that real time reporting doesn't impact
production systems. Apparently, there are some applications that have
caused problems against performance of real time systems with some
reports.
I'm more of less just looking for some existing topology patterns that
have been proven to satisfy the needs of the business. Our organization
has about 2000 employees all over the world, and we have many systems
in place, SQL Server as well as Oracle.
My personal feeling is that any real time reporting be designed with
performance in mind and anything else should pull from the data
warehouse, with the granularity as designed into the reporting and
model requirements.
I wish I had a good case example, but my main motivation for
approaching this issue is that another developer on our team made a
proposal to actuall replicate the entire prod environment for all real
time reporting. I'm having a hard time buying into that idea.
Thanks for any insight!
Cheers!
-Sean
|||Hi Sean,
sure.....there are some useful books to read on my beginners page.
http://www.peternolan.com/Beginners/...0/Default.aspx
The best book on architecture is the Corporate Information Factory book
by Bill et al.
http://www.amazon.com/exec/obidos/AS...700697-6194236
Though for some reason Amazons links are not working at the moment.
CIF is a well defined and well articulated architecture for designers
to take into consideration when building end to end Information
Infrastructure for sizable companies. Well worth reading...
Peter
|||Thank you very much Peter!
I'll be checking them out today!
Cheers!
-Sean
Best practices for reporting: Replicated servers + data warehouse server?
Good afternoon,
Lately there has been discussion of what the best practice would be for
enterprise reporting needs. Specifically, we currently have online OLTP
servers for our business apps, as well as a data warehouse server.
A member of our team has suggested that we create a new server farm (or
server) for real time reporting to alleviate the real time reporting
burden off the production servers.
I was looking for any advice or pointers to design a topology that can
support our current real time systems as well as data warehousing
needs, while still minimizing the burden of reporting against our live
production servers.
The mention of replication has come to mind, but I'm not completely
sold on attempting to replicate our production data for the "heck of
it"...
Any thoughts?
Best regards,
-SeanCan you specify requirements for the reporting data? What would be the
maximum latency involved? I assume that reporting from dw isnt good enough
beacause of the latency...
MC
"Sean Aitken" <sean.aitken@.gmail.com> wrote in message
news:1139858184.012942.18310@.g14g2000cwa.googlegroups.com...
> Good afternoon,
> Lately there has been discussion of what the best practice would be for
> enterprise reporting needs. Specifically, we currently have online OLTP
> servers for our business apps, as well as a data warehouse server.
> A member of our team has suggested that we create a new server farm (or
> server) for real time reporting to alleviate the real time reporting
> burden off the production servers.
> I was looking for any advice or pointers to design a topology that can
> support our current real time systems as well as data warehousing
> needs, while still minimizing the burden of reporting against our live
> production servers.
> The mention of replication has come to mind, but I'm not completely
> sold on attempting to replicate our production data for the "heck of
> it"...
> Any thoughts?
> Best regards,
> -Sean
>|||Good morning MC,
There aren't any real specific requirements at this time. But, we do
have the need to ensure that real time reporting doesn't impact
production systems. Apparently, there are some applications that have
caused problems against performance of real time systems with some
reports.
I'm more of less just looking for some existing topology patterns that
have been proven to satisfy the needs of the business. Our organization
has about 2000 employees all over the world, and we have many systems
in place, SQL Server as well as Oracle.
My personal feeling is that any real time reporting be designed with
performance in mind and anything else should pull from the data
warehouse, with the granularity as designed into the reporting and
model requirements.
I wish I had a good case example, but my main motivation for
approaching this issue is that another developer on our team made a
proposal to actuall replicate the entire prod environment for all real
time reporting. I'm having a hard time buying into that idea.
Thanks for any insight!
Cheers!
-Sean|||Hi Sean,
sure.....there are some useful books to read on my beginners page.
http://www.peternolan.com/Beginners...40/Default.aspx
The best book on architecture is the Corporate Information Factory book
by Bill et al.
http://www.amazon.com/exec/obidos/A...5700697-6194236
Though for some reason Amazons links are not working at the moment.
CIF is a well defined and well articulated architecture for designers
to take into consideration when building end to end Information
Infrastructure for sizable companies. Well worth reading...
Peter|||Thank you very much Peter!
I'll be checking them out today!
Cheers!
-Sean
Lately there has been discussion of what the best practice would be for
enterprise reporting needs. Specifically, we currently have online OLTP
servers for our business apps, as well as a data warehouse server.
A member of our team has suggested that we create a new server farm (or
server) for real time reporting to alleviate the real time reporting
burden off the production servers.
I was looking for any advice or pointers to design a topology that can
support our current real time systems as well as data warehousing
needs, while still minimizing the burden of reporting against our live
production servers.
The mention of replication has come to mind, but I'm not completely
sold on attempting to replicate our production data for the "heck of
it"...
Any thoughts?
Best regards,
-SeanCan you specify requirements for the reporting data? What would be the
maximum latency involved? I assume that reporting from dw isnt good enough
beacause of the latency...
MC
"Sean Aitken" <sean.aitken@.gmail.com> wrote in message
news:1139858184.012942.18310@.g14g2000cwa.googlegroups.com...
> Good afternoon,
> Lately there has been discussion of what the best practice would be for
> enterprise reporting needs. Specifically, we currently have online OLTP
> servers for our business apps, as well as a data warehouse server.
> A member of our team has suggested that we create a new server farm (or
> server) for real time reporting to alleviate the real time reporting
> burden off the production servers.
> I was looking for any advice or pointers to design a topology that can
> support our current real time systems as well as data warehousing
> needs, while still minimizing the burden of reporting against our live
> production servers.
> The mention of replication has come to mind, but I'm not completely
> sold on attempting to replicate our production data for the "heck of
> it"...
> Any thoughts?
> Best regards,
> -Sean
>|||Good morning MC,
There aren't any real specific requirements at this time. But, we do
have the need to ensure that real time reporting doesn't impact
production systems. Apparently, there are some applications that have
caused problems against performance of real time systems with some
reports.
I'm more of less just looking for some existing topology patterns that
have been proven to satisfy the needs of the business. Our organization
has about 2000 employees all over the world, and we have many systems
in place, SQL Server as well as Oracle.
My personal feeling is that any real time reporting be designed with
performance in mind and anything else should pull from the data
warehouse, with the granularity as designed into the reporting and
model requirements.
I wish I had a good case example, but my main motivation for
approaching this issue is that another developer on our team made a
proposal to actuall replicate the entire prod environment for all real
time reporting. I'm having a hard time buying into that idea.
Thanks for any insight!
Cheers!
-Sean|||Hi Sean,
sure.....there are some useful books to read on my beginners page.
http://www.peternolan.com/Beginners...40/Default.aspx
The best book on architecture is the Corporate Information Factory book
by Bill et al.
http://www.amazon.com/exec/obidos/A...5700697-6194236
Though for some reason Amazons links are not working at the moment.
CIF is a well defined and well articulated architecture for designers
to take into consideration when building end to end Information
Infrastructure for sizable companies. Well worth reading...
Peter|||Thank you very much Peter!
I'll be checking them out today!
Cheers!
-Sean
Tuesday, March 20, 2012
Best practices for backup and restore of large replicated databases
Hello,
I am working on a project where we have a large database that is replicated.
My task is to create a backup/restore plan for this project.
I know the basics for backup/restore, but are looking for best practices and
experiences from you who have done this already on a large scale.
Both publisher and subscribers are allowed to do updates. Restore time at
subscribers have to be short and therefore I am looking for a solution where
both publisher and subscribers are backup up.
Thanks in advance for any pointers to white papers, books, emails, etc.
Best regards,
Vemund
Hi Vemund,
Thanks for your post and I would like to take ownership of this thread and
help you with this issue.
Based on my scope, I understood that you would like to find the best
practise about large scale replicated database backup and restore strategy.
Have I understood you? Correct me if I was wrong.
First of all, please understood that it is hard to say what is the best
practice for your porject as I am not sure how much important is the data
for you and how much data lose could be acceptable. You should discussed
this with your users and find the best strategy for your project.
Generally speaking, here are some general recommendations on database
disaster recovery strategy:
1. Make regular backups that reflect your backup strategy. Ask yourselves
the question - how much data can I afford to lose? (1 min, 1 hour, 1 day
worth). Then ensure that the frequency of the backups reflects the answer
to that question.
2. Make backup to a different media from where the database file reside.
That way you protect against a central point of failure. You can consider
backing up to tape or to another disk (share). Consider moving the backed
up data off-site to another location to protect against disasters like
fires, floods, hurricanes, tornadoes, etc.
3. Test your backups on a regular basis by ensuring they are restore-able
on another system. Then run DBCC CHECKDB on those to ensure the original
database or the backup are not damaged.
4. Keep in mind that the data stored in your database(s) may be one of the
most valuable assets your organization has!
In addition, please review the following information in SQL Books On-Line
"Designing a Backup and Restore Strategy"
Here are some precautions a database administrator should take to ensure
the safety of the data:
1. It is always a good idea to ensure that your backup strategy is
sufficient to recover from a catastrophic failure. Offsite storage and
other precautions are appropriate.
2. Test the database restore operation in a secondary or test database on a
frequent basis.
3. Ensure that any caching devices can handle all failure situations (power
outage, bad sectors, bad drives, system outage, lockups, power spike, and
so forth).
4. Ensure that your caching device:
-- Has integrated battery backup.
-- Can reissue writes on power up.
-- Can be fully disabled if necessary.
-- Handles bad sector re-mapping realtime.
5. Enable torn page detection; it has little performance impact.
6. Configure RAID drives allowing for a hot swap of a bad disk drive, if
possible.
7. Use newer caching controllers that allow addition of more disk space
without
restarting the OS. This can be an ideal solution.
Check the following which covers some of this in detail:
SQL Server capacity and storage guide:
http://www.microsoft.com/technet/pro...n/sqlops6.mspx
Windows backup guide:
http://www.microsoft.com/technet/pro...aintain/backup
rest/br04.mspx
Moreover, here is one more WebCast for you, which will discuss Microsoft
SQL Server database files, the different kinds of database backup options
that are available in SQL Server 2000, and some of the factors to consider
when you design a backup strategy. It will talk about best practices for
backing up and restoring database files. It will review some case studies
and talk about how to troubleshoot problems with backup and restore
operations.
Support WebCast: Microsoft SQL Server 2000: Understanding Backup and Restore
http://support.microsoft.com/?id=329833
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Vemund,
I am just checking on your progress regarding the information that was sent
you! I wonder whether my suggestion is useful or you would like to receive
more information on this.
If you encounter any difficulty, please do not hesitate to let me know.
Please post here and let me know the status of your issue. Looking forward
to hearing from you soon
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
sql
I am working on a project where we have a large database that is replicated.
My task is to create a backup/restore plan for this project.
I know the basics for backup/restore, but are looking for best practices and
experiences from you who have done this already on a large scale.
Both publisher and subscribers are allowed to do updates. Restore time at
subscribers have to be short and therefore I am looking for a solution where
both publisher and subscribers are backup up.
Thanks in advance for any pointers to white papers, books, emails, etc.
Best regards,
Vemund
Hi Vemund,
Thanks for your post and I would like to take ownership of this thread and
help you with this issue.
Based on my scope, I understood that you would like to find the best
practise about large scale replicated database backup and restore strategy.
Have I understood you? Correct me if I was wrong.
First of all, please understood that it is hard to say what is the best
practice for your porject as I am not sure how much important is the data
for you and how much data lose could be acceptable. You should discussed
this with your users and find the best strategy for your project.
Generally speaking, here are some general recommendations on database
disaster recovery strategy:
1. Make regular backups that reflect your backup strategy. Ask yourselves
the question - how much data can I afford to lose? (1 min, 1 hour, 1 day
worth). Then ensure that the frequency of the backups reflects the answer
to that question.
2. Make backup to a different media from where the database file reside.
That way you protect against a central point of failure. You can consider
backing up to tape or to another disk (share). Consider moving the backed
up data off-site to another location to protect against disasters like
fires, floods, hurricanes, tornadoes, etc.
3. Test your backups on a regular basis by ensuring they are restore-able
on another system. Then run DBCC CHECKDB on those to ensure the original
database or the backup are not damaged.
4. Keep in mind that the data stored in your database(s) may be one of the
most valuable assets your organization has!
In addition, please review the following information in SQL Books On-Line
"Designing a Backup and Restore Strategy"
Here are some precautions a database administrator should take to ensure
the safety of the data:
1. It is always a good idea to ensure that your backup strategy is
sufficient to recover from a catastrophic failure. Offsite storage and
other precautions are appropriate.
2. Test the database restore operation in a secondary or test database on a
frequent basis.
3. Ensure that any caching devices can handle all failure situations (power
outage, bad sectors, bad drives, system outage, lockups, power spike, and
so forth).
4. Ensure that your caching device:
-- Has integrated battery backup.
-- Can reissue writes on power up.
-- Can be fully disabled if necessary.
-- Handles bad sector re-mapping realtime.
5. Enable torn page detection; it has little performance impact.
6. Configure RAID drives allowing for a hot swap of a bad disk drive, if
possible.
7. Use newer caching controllers that allow addition of more disk space
without
restarting the OS. This can be an ideal solution.
Check the following which covers some of this in detail:
SQL Server capacity and storage guide:
http://www.microsoft.com/technet/pro...n/sqlops6.mspx
Windows backup guide:
http://www.microsoft.com/technet/pro...aintain/backup
rest/br04.mspx
Moreover, here is one more WebCast for you, which will discuss Microsoft
SQL Server database files, the different kinds of database backup options
that are available in SQL Server 2000, and some of the factors to consider
when you design a backup strategy. It will talk about best practices for
backing up and restoring database files. It will review some case studies
and talk about how to troubleshoot problems with backup and restore
operations.
Support WebCast: Microsoft SQL Server 2000: Understanding Backup and Restore
http://support.microsoft.com/?id=329833
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Vemund,
I am just checking on your progress regarding the information that was sent
you! I wonder whether my suggestion is useful or you would like to receive
more information on this.
If you encounter any difficulty, please do not hesitate to let me know.
Please post here and let me know the status of your issue. Looking forward
to hearing from you soon
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
sql
Subscribe to:
Posts (Atom)