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.Jonathan
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#9 -- Move
Databases between computers running by sql server
"Jonathan" <nielsonj1976@.yahoo.co.uk> wrote in message
news:9db42c2b.0310200347.50044387@.posting.google.com...
> 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.|||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" <nielsonj1976@.yahoo.co.uk> wrote in message
news:9db42c2b.0310200347.50044387@.posting.google.com...
> 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.|||Jonathan (nielsonj1976@.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, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

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.Jonathan

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

"Jonathan" <nielsonj1976@.yahoo.co.uk> wrote in message
news:9db42c2b.0310200347.50044387@.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.|||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" <nielsonj1976@.yahoo.co.uk> wrote in message
news:9db42c2b.0310200347.50044387@.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.|||Jonathan (nielsonj1976@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

creating test SQL Server environments

Hi,

I was wondering if anyone has a neat (preferably automated) method of creating small testing databases from large production instances.
My requirement would be to copy the schema and a subset of configuration data from a production database into a test database. The subset of data would be a full copy of a subset of tables, rather than a subset of data within one or more tables. There is a mixture of SQL2000 and SQL2005 servers involved in this requirement. I'm familar with the scripting mechanisms of Enterprise Manager and Management studio and DTS packages, sufficent to perform a process like this manually, but want to productionise and schedule this process to be performed automatically.
I'm sure this must be a commonly performed task, so I'm interested to know if anyone has a "best practice" for this requirement.

Thanks,

BillI've always used backup/restore for this. of course that doesn't give you a subset of anything.

for me the issue with taking a subset would be: if in test you don't have all the data that prod is working with, how do you really know your stuff will work in prod?|||I've always used backup/restore for this. of course that doesn't give you a subset of anything.

for me the issue with taking a subset would be: if in test you don't have all the data that prod is working with, how do you really know your stuff will work in prod?

Thanks Jezemine, The problem with backup restore, is that my production database is 3Tb+. However nearly all of this is historic transactional data. A small subset (1 or 2 Gb) is the configuration data that dictates how any transactions I post into the database will process. So it is this data (which is as important as source code wrt to the behaviour of the system) that I want to replicate to any test environment.|||This won't help much at this point, but I frequently set up file groups in Microsoft SQL databases for just this purpose. The filegroups can be backed up individually, so it is easy to "pick and choose" at the table level what data you want.

If you are doing this "after the fact" where you already have a 3Tb database, my first thought would be to script the schema of the whole tamale (all tables) and play that script into an empty database container. Then I'd pick one of (linked server, DTS, BCP, copy wizard) to move the data from the production database to the container with only the schema in it.

-PatPsql

Creating Production Schedule report

Hopefully this will be easy, but as someone that is new to CR9, I am having problems getting this right.

Here is the scenario: I need a production report. We run 3 shifts 24/7. shift 1 is from 7AM to 3PM, shift 2 is from 3PM to 11PM, and shift 3 is from 11PM to 7AM. I will need the report to be able to look at any days, or range of days, production. Easy enough so far, this is basic. What I am having issues with is handling the 3rd shift information.

Since 3rd shift spans 2 physical dates it needs to be part of the previous dates production(days start at 7AM not 12AM). I am just confused about how to get CR9 to handle this scenario.

example:
If 12/22/04 is selected, I need the report to group each shifts data for that day so I would have -
Production Date 12/22/04
Shift 1 (date 12/22/04 starting at 7AM, stopping at 3PM)
<shift data>
Shift 2 (date 12/22/04 starting at 3PM, stopping at 11PM)
<shift data>
Shift 3 (date 12/22/04 AND 12/23/04 starting at 11PM, stopping at 7AM)
<shift data from 11PM to 11:59PM would fall properly, 12AM to 7AM have different date but are part of 12/22/04 production>
<EOR>

Data available is: date, time, shift #, numeric data pertaining to shift production.

Any help is appreciated.Well, this looks more like a data managment/structuring problem than a Crystal problem.
Here's my take on it:
Your shifts all get allocated a day on which they start, and then a shift type.
So for 3 Jan 2005 the data will look like this:
DATE SHIFT PRODUCTION
03/01/2005 D 507
03/01/2005 A 655
03/01/2005 N 489

(I've made up some production data just to give you the general idea).

So then when you structure your query, you're not looking at the timestamp that the data was entered on, your using the DATE field to group you data by date, and then your SHIFT field to break it down to shift level.
So it doesn't matter what date they finished on, the date entered is the day they started.

Dave

Creating new File groups on an existing db

Out production database is around 10G and it is all in one file group
(Primary).
I would like to create new file groups and split the database into multiple
file groups for better manageablity.
I have identified the tables that can be moved into new file groups.
What is the best approach to spilt an existing production database into
multiple file groups?
Thanks,
S.KumarSuresh
If your table whose are going to be place on separate filegroup have
clustered indexes then you can easily re-create the clustered index with
specify file group .For details please refer to BOL.
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> Out production database is around 10G and it is all in one file group
> (Primary).
> I would like to create new file groups and split the database into
multiple
> file groups for better manageablity.
> I have identified the tables that can be moved into new file groups.
> What is the best approach to spilt an existing production database into
> multiple file groups?
> Thanks,
> S.Kumar
>|||You should start changing the filegroups for the specified tables, if there
aren´t many tables you could work with EM, edit table, property of the
tables/ indexes.
Jens Süßmeyer.|||Multiple files in a single filegroup allows SQL to do parallel IO on a
single query...
Using multiple filegroups allow you to.
1. Backup/Restore subsets of tables with different recovery needs.
2. Balance IO if you beleive you can do better than striping
3. Limit the space allocation which is used by a subset of tables...
The 1st is the best reason for filegroups...It does not improve
manageability, but causes you to have to watch for disk space utilization on
EACH filegroup...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corp (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:OxBFFmWbDHA.3360@.tk2msftngp13.phx.gbl...
> My aim is to split the database into multiple files.
> I could have multiple files referring to the same File Group (Primary) or
> multiple files going to multiple File groups.
> Which way is better and what are the pros and cons?
> Thanks,
> S.Kumar
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eki5qUWbDHA.1580@.tk2msftngp13.phx.gbl...
> > Suresh
> > If your table whose are going to be place on separate filegroup have
> > clustered indexes then you can easily re-create the clustered index with
> > specify file group .For details please refer to BOL.
> >
> >
> >
> > "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> > news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> > > Out production database is around 10G and it is all in one file group
> > > (Primary).
> > > I would like to create new file groups and split the database into
> > multiple
> > > file groups for better manageablity.
> > >
> > > I have identified the tables that can be moved into new file groups.
> > > What is the best approach to spilt an existing production database
into
> > > multiple file groups?
> > >
> > > Thanks,
> > > S.Kumar
> > >
> > >
> >
> >
>|||Wayne,
Did you use to work for Unisys ?
I use to know one Wayne Snyder when I worked for them in Charlotte 10 yrs
ago.
If you are the one, please send me a note to my personal email address.
Thanks,
Suresh Kumar
"Wayne Snyder" <wsnyder@.ikon.com> wrote in message
news:OyKlrSXbDHA.2672@.tk2msftngp13.phx.gbl...
> Multiple files in a single filegroup allows SQL to do parallel IO on a
> single query...
> Using multiple filegroups allow you to.
> 1. Backup/Restore subsets of tables with different recovery needs.
> 2. Balance IO if you beleive you can do better than striping
> 3. Limit the space allocation which is used by a subset of tables...
> The 1st is the best reason for filegroups...It does not improve
> manageability, but causes you to have to watch for disk space utilization
on
> EACH filegroup...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Computer Education Services Corp (CESC), Charlotte, NC
> (Please respond only to the newsgroups.)
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> news:OxBFFmWbDHA.3360@.tk2msftngp13.phx.gbl...
> > My aim is to split the database into multiple files.
> > I could have multiple files referring to the same File Group (Primary)
or
> > multiple files going to multiple File groups.
> >
> > Which way is better and what are the pros and cons?
> > Thanks,
> > S.Kumar
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:eki5qUWbDHA.1580@.tk2msftngp13.phx.gbl...
> > > Suresh
> > > If your table whose are going to be place on separate filegroup have
> > > clustered indexes then you can easily re-create the clustered index
with
> > > specify file group .For details please refer to BOL.
> > >
> > >
> > >
> > > "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> > > news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> > > > Out production database is around 10G and it is all in one file
group
> > > > (Primary).
> > > > I would like to create new file groups and split the database into
> > > multiple
> > > > file groups for better manageablity.
> > > >
> > > > I have identified the tables that can be moved into new file groups.
> > > > What is the best approach to spilt an existing production database
> into
> > > > multiple file groups?
> > > >
> > > > Thanks,
> > > > S.Kumar
> > > >
> > > >
> > >
> > >
> >
> >
>

Creating New Empty Database

Hello, I have a production database that I need to recreate somewhere else
but with no data in the tables. Is the best way to do this, simply using the
'Generate SQL Script' option though Enterprise Manager against the existing
database. Are there any objects that will not get created?
One thing I have come across with scripting so far is that I have views
based on views. So I'm having to manually change the order of views in my
script to have the ones not based on views at the top. Is there a easier way
to do this?
Thanks
Don
Hope the server is 2000 itself, the best way is by generate script only. You
will get all the objects asscoiated with one DB when using generate script.
Again the question regarding views, after generating the script reorder the
views according to your need. While sciprting it will get generated in the
order in which each object was created, so there wont be much work for you.
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Don" wrote:

> Hello, I have a production database that I need to recreate somewhere else
> but with no data in the tables. Is the best way to do this, simply using the
> 'Generate SQL Script' option though Enterprise Manager against the existing
> database. Are there any objects that will not get created?
> One thing I have come across with scripting so far is that I have views
> based on views. So I'm having to manually change the order of views in my
> script to have the ones not based on views at the top. Is there a easier way
> to do this?
> Thanks
> Don

Creating New Empty Database

Hello, I have a production database that I need to recreate somewhere else
but with no data in the tables. Is the best way to do this, simply using the
'Generate SQL Script' option though Enterprise Manager against the existing
database. Are there any objects that will not get created?
One thing I have come across with scripting so far is that I have views
based on views. So I'm having to manually change the order of views in my
script to have the ones not based on views at the top. Is there a easier way
to do this?
Thanks
DonHope the server is 2000 itself, the best way is by generate script only. You
will get all the objects asscoiated with one DB when using generate script.
Again the question regarding views, after generating the script reorder the
views according to your need. While sciprting it will get generated in the
order in which each object was created, so there wont be much work for you.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Don" wrote:
> Hello, I have a production database that I need to recreate somewhere else
> but with no data in the tables. Is the best way to do this, simply using the
> 'Generate SQL Script' option though Enterprise Manager against the existing
> database. Are there any objects that will not get created?
> One thing I have come across with scripting so far is that I have views
> based on views. So I'm having to manually change the order of views in my
> script to have the ones not based on views at the top. Is there a easier way
> to do this?
> Thanks
> Don

Creating New Empty Database

Hello, I have a production database that I need to recreate somewhere else
but with no data in the tables. Is the best way to do this, simply using th
e
'Generate SQL Script' option though Enterprise Manager against the existing
database. Are there any objects that will not get created?
One thing I have come across with scripting so far is that I have views
based on views. So I'm having to manually change the order of views in my
script to have the ones not based on views at the top. Is there a easier wa
y
to do this?
Thanks
DonHope the server is 2000 itself, the best way is by generate script only. You
will get all the objects asscoiated with one DB when using generate script.
Again the question regarding views, after generating the script reorder the
views according to your need. While sciprting it will get generated in the
order in which each object was created, so there wont be much work for you.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Don" wrote:

> Hello, I have a production database that I need to recreate somewhere else
> but with no data in the tables. Is the best way to do this, simply using
the
> 'Generate SQL Script' option though Enterprise Manager against the existin
g
> database. Are there any objects that will not get created?
> One thing I have come across with scripting so far is that I have views
> based on views. So I'm having to manually change the order of views in my
> script to have the ones not based on views at the top. Is there a easier
way
> to do this?
> Thanks
> Don