473,392 Members | 1,355 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,392 software developers and data experts.

Creation of reporting database

We have a production database, but due to the crippling load of some
reporting tools, we are looking to have a separate reporting database.
Due to it's nature, the reporting database doesn't need to be as up
to date as the master, perhaps it could be refreshed every evening?

What I wish to know is the best way to go about this. Replicating a
whole database seems excessive as there are 7000+ tables and we don't
need it so closely matching the production database. Log shipping
also seems out as we need the reporting database to be highly
available and if we keep applying transaction logs ever 15 minutes or
so, we need to kick all the users out.

Is there a way of scripting or automatically getting SQL Server 2k to
copy over the last full backup it did and apply this to another
server? Also if we used this method, what would happen to the users
stored in the database? Would I have to keep re-adding them after the
upgrade?

Is there a better way to tackle this problem? All help gratefully
received. We are running SQL Server 2000 Enterprise Edition on a
Windows 2000 Server machine.

Thanks.
Jul 20 '05 #1
3 2437
Jonathan

http://support.microsoft.com/default...n-us;Q314546#9 ----- Move
Databases between computers running by sql server



"Jonathan" <ni**********@yahoo.co.uk> wrote in message
news:9d**************************@posting.google.c om...
We have a production database, but due to the crippling load of some
reporting tools, we are looking to have a separate reporting database.
Due to it's nature, the reporting database doesn't need to be as up
to date as the master, perhaps it could be refreshed every evening?

What I wish to know is the best way to go about this. Replicating a
whole database seems excessive as there are 7000+ tables and we don't
need it so closely matching the production database. Log shipping
also seems out as we need the reporting database to be highly
available and if we keep applying transaction logs ever 15 minutes or
so, we need to kick all the users out.

Is there a way of scripting or automatically getting SQL Server 2k to
copy over the last full backup it did and apply this to another
server? Also if we used this method, what would happen to the users
stored in the database? Would I have to keep re-adding them after the
upgrade?

Is there a better way to tackle this problem? All help gratefully
received. We are running SQL Server 2000 Enterprise Edition on a
Windows 2000 Server machine.

Thanks.

Jul 20 '05 #2
Personally I would

1. Identify the tables that you need to report on.
2. Take them over to the reporting server
3. Create triggers on your Source tables and have them capture the data
that is entered, updated or deleted in those tables to a seperate table.
4. Build a DTS package that is fired by the SQL Server Agent every night
that will pick up those changes and migrate them to your reporting server.

This means that you are only taking across the things you need to. Using
the backup and restore method is easy but you will need to possibly unorphan
logins and also even if nothing changes during the day you are still going
to take across the whole database whereas in the other method you wouldn't.

Just my 0.02

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Jonathan" <ni**********@yahoo.co.uk> wrote in message
news:9d**************************@posting.google.c om...
We have a production database, but due to the crippling load of some
reporting tools, we are looking to have a separate reporting database.
Due to it's nature, the reporting database doesn't need to be as up
to date as the master, perhaps it could be refreshed every evening?

What I wish to know is the best way to go about this. Replicating a
whole database seems excessive as there are 7000+ tables and we don't
need it so closely matching the production database. Log shipping
also seems out as we need the reporting database to be highly
available and if we keep applying transaction logs ever 15 minutes or
so, we need to kick all the users out.

Is there a way of scripting or automatically getting SQL Server 2k to
copy over the last full backup it did and apply this to another
server? Also if we used this method, what would happen to the users
stored in the database? Would I have to keep re-adding them after the
upgrade?

Is there a better way to tackle this problem? All help gratefully
received. We are running SQL Server 2000 Enterprise Edition on a
Windows 2000 Server machine.

Thanks.

Jul 20 '05 #3
Jonathan (ni**********@yahoo.co.uk) writes:
We have a production database, but due to the crippling load of some
reporting tools, we are looking to have a separate reporting database.
Due to it's nature, the reporting database doesn't need to be as up
to date as the master, perhaps it could be refreshed every evening?

What I wish to know is the best way to go about this. Replicating a
whole database seems excessive as there are 7000+ tables and we don't
need it so closely matching the production database. Log shipping
also seems out as we need the reporting database to be highly
available and if we keep applying transaction logs ever 15 minutes or
so, we need to kick all the users out.
If you only need a daily refresh, there is no reason to apply logs
every 15 minutes. Once a day would do.

Of course with that low frequency, moving the entire database may
be a more palatable task. As Allan pointed out, you need to fix
users and login each time then, whereas with log shipping you would
only need to cater for new users.
Is there a way of scripting or automatically getting SQL Server 2k to
copy over the last full backup it did and apply this to another
server?


Yes, you use the RESTORE and BACKUP commands. Read about them in Books
Online. And then you need to use some COPY command in the command-line
world to copy the backup file as it is not adviceable to backup or
restore from a network device. All this can be handled in a job from
SQL Agent.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Alex | last post by:
Subject: Looking for an XML (database-based) Query Reporting Tool/advice First off, let me apologize if this thread is somewhat off topic... PLEASE REPLY TO: xml@solex-bi.com I am looking...
3
by: Paul Say | last post by:
I have a asp.net application that will need to create a document/report from data in a database. The user will fill out a form supplying various criteria for parameters. I would like the...
8
by: Woody Splawn | last post by:
I am asking this question here because I asked this question in the Reporting Services Newsgroup and did not get an answer. Does anyone know if Reporting Services is intended to work in a...
5
by: MLH | last post by:
A97 aborts creation of MDE reporting that there's a compile error in one form. Sure enough, if I remove the form from the source database and attempt to compile the MDE, it succeeds. But when I...
1
by: Larry Dooley | last post by:
Here's my issue. We've decided to replace a very critical (without it the business would lose lots of money) departmental reporting system with a built from scratch system based on .NET. The key...
1
by: iThinkData | last post by:
Should these 3 items, .Net 2.0 Website, SQLServer 2005, Reporting Services 2005. all reside on the same computer? I know that the website issues a configuration error saying it can't find...
1
by: rmk | last post by:
How can I get the 2000 and 2005 versions of SQL Server Reporting Services both working on my development laptop ????? I have ASP.NET 1.1 and 2.0 installed on my laptop. I have Visual Studio...
3
by: antonyliu2002 | last post by:
I am looking for a free reporting package for .net 2.0 against Oracle database 10g. The web application I am working on will let office staff members who don't know much about database generate...
0
by: fperri | last post by:
Hi, I am trying to configure reporting services which I just added to an already existing installation of SQL Server 2005. When I am in the reporting services configuration manager and I am trying...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.