Best Technology for Reporting.

Current Environment:
SQL 2000 SP4 EE on Windows 2003 SP1
I need some suggestion on some of the currently available options for
reporting.
We have a transactional database where lots of transactions come throughout
the day. Some of the tables can have over 200 k records added. We need to
aggregate data on another server for reporting purposes and currently all
this is done via DTS jobs which do lots of locking and time consuming.
Since we are looking into re-architecting this solution and go away from
DTS, I am looking for various options that we can set up in test environment
and see which one supercedes over another one. Also, should I look into SQL
2005 or stay with SQL 2000 only.
Please advice.
Thanks in Advance."Mark" <Mark@.discussions.microsoft.com> wrote in message
news:8F1E6BD8-A36D-4D3E-8799-C67DF4F19D9D@.microsoft.com...
> Current Environment:
> SQL 2000 SP4 EE on Windows 2003 SP1
> I need some suggestion on some of the currently available options for
> reporting.
> We have a transactional database where lots of transactions come
> throughout
> the day. Some of the tables can have over 200 k records added. We need to
> aggregate data on another server for reporting purposes and currently all
> this is done via DTS jobs which do lots of locking and time consuming.
> Since we are looking into re-architecting this solution and go away from
> DTS, I am looking for various options that we can set up in test
> environment
> and see which one supercedes over another one. Also, should I look into
> SQL
> 2005 or stay with SQL 2000 only.
> Please advice.
> Thanks in Advance.
If the jobs are doing that much locking, then I would probably look at
rearchitecting the jobs themselves. Take advantage of the WITH (NOLOCK)
hints where appropriate etc.
Having your reporting done on the live data with the same aggregations that
your DTS jobs are doing doesn't seem like a particularly good idea to me.
Your performance will be better on the aggregated data already stored in the
reporting database server.
Just my .02
Rick Sawtell
MCT, MCSD, MCDBAsql

Best solution, iterate over millions records and call extended

In fact, the functionarity needs to be available on the server. So I will
created a program, maybe C# or C++ program to do the looping and calculate
and put the executible on the server so it can be launched via xp_cmdshell..
.
It should be better than big TSQL cursor?
"JT" wrote:

> If you have a situation that calls for looping through a cursor, then it's
> better to implement the cursor on the client side than on the server. Open
a
> read-only, forward only ADO recordset and Command.Execute the stored
> procedure for each row.
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
>
>In fact, my question is
Transact-SQL script "declare cursor" (fast forward) vs. Client side ADO code
with fast forward server cursor
Which one is better for very large rows?
"nick" wrote:
> In fact, the functionarity needs to be available on the server. So I will
> created a program, maybe C# or C++ program to do the looping and calculate
> and put the executible on the server so it can be launched via xp_cmdshell
..
> It should be better than big TSQL cursor?
> "JT" wrote:
>

Best Practices Analyzer for Sql Server 2005

Hi, Is there a version for this tool available for 2005? or do you know if new release is comming soon ?

Regards.

There is no version available yet. I haven't heard of any coming releases yet.|||

I'm a program manager at Microsoft in SQL Server. We've decided to make a new Best Practices Analyzer tool which will support SQL Server 2005. For more information or to provide some feedback go to: http://blogs.msdn.com/sqlrem/archive/2006/04/06/570292.aspx.

Thanks,

Paul A. Mestemaker II
Program Manager
Microsoft Corporation
SQL Server Relational Engine Manageability Team

Best Practices Analyzer for Sql Server 2005

Hi, Is there a version for this tool available for 2005? or do you know if new release is comming soon ?

Regards.

There is no version available yet. I haven't heard of any coming releases yet.|||

I'm a program manager at Microsoft in SQL Server. We've decided to make a new Best Practices Analyzer tool which will support SQL Server 2005. For more information or to provide some feedback go to: http://blogs.msdn.com/sqlrem/archive/2006/04/06/570292.aspx.

Thanks,

Paul A. Mestemaker II
Program Manager
Microsoft Corporation
SQL Server Relational Engine Manageability Team

best option when master db is not available

i want to setup my database to new server machine. i have backup as well as copy of database files (data & log), but i lost my master database from existing server.

so i have 2 ways of having my database

1. restore from backup

2. attach the data files.

which one is best option, when we start afresh on new machine with new master database.

Either method should work. I would prefer the RESTORE.

IF the file location (folder) is different, you may need to add the WITH MOVE option to the RESTORE.

Refer to Books Online, Topic: RESTORE

|||yes both options will work.......i'll advise you to go with restore db from backup but ensure that you start SQL Server in single user mode and then only you can perform restoration..........refer BOL its the best resource|||

thanks for your replies. I have gone thru the BOL.

One thing i need to reconfirm before i proceed, with the loss of master database i lost all the information that the master database hold, in this situation, which of the option will be best.

|||

i feel that there is some confusion in the requirement. ie Whether u r trying to resotre Master Database or User database. To restore a master database of one machine to another machine you have many restriction. OS/SQL SErver Version/Service pack and configuration (if i remember correctly) should be the same. I have also read somewhere that the backup of the same physical machine can ionly be restored(i have never tried this).

To transfer the objects from one server to other there are scripts available. You need to transfer Login/Jobs/DTS. THis is possible through Scripts.

(a) Install new instance of sql server in new machine

(b) transfer the LOgin refer : http://support.microsoft.com/default.aspx/kb/246133

(c) Make script of JOBs and run the script in the destination

(d) use save as option or file object trasfer for DTS

(e) Use Backup /restore for user databases.

Madhu

|||

there should not be any confusion, i want to restore user database.

since my old master db is lost, i lost login, etc. logins i can recreate manually, but i am worried is there is thing else about my User database which is lost along with master db, which i may not recover.

|||

All of the data in the users databases should be intact.

Any Jobs would be in the msdb database, so if you didn't lose msdb, you still have the jobs.

Most likely, the only thing lost is logins.

Best backup plan for 24*7 running production database

Hi:
ENVIRONMENT:WIN2000/SQL2000
We need best backup plan for one our 24*7 available
production database growing by 1.2 gb per day..
Any one can tell me the best plan or can recommend any
best site for this?
Thanks in advance
PuruPuru
For large databases, which yours soon will be, most people
would take advantage of differential backups to reduce
impact on the system.
Something like a full backup once a week, and
differentials the other days and regular transaction log
backups as required.
Early in the life of your database you may find towards
the end of the week your differentials are nearly a large
and take as much time as a full backup, due to volume of
changes, as the database gets bigger that will change.
Try to put your backups on seperate disks to your database
and transaction logs to reduce the impact of them running.
Try to schedule them for the quietest time of the day,
even with a 24 by 7 installation you will still have some
times that are quieter than others. (usually when peak
business hours are in the paciffic ocean).
If your database gets into the terra-bytes you may need to
come up with a different strategy.
Hope this helps
Regards
John

Beginner seeks help

My company needs a database. My first thought was to do it in Access, because it's available! But I wanted to check that that was smart, what its limitations are, when it's better to move to bigger / more expensive software, etc.

It's probably going to have several hundred thousand records in and will grow by more than a hundred thousand every year. Is there a size limit?

Not that many fields, though - it's not that complicated a database.

Many thanks in advance for any help :beer:MS Access 2000 has a 2 gig limit.|||Does it still have the 1 gig limit on table size? Even that would suffice for this user's requirements, most likely.|||For this situation, Access should be doable, but here are a few things to consider:

1. How many users are you going to need to support?
2. How many concurrent users do you expect at any given time?

Hope this helps in your decision-making...

-Tim

P.S. - got any more beer? :D|||If you are looking for a low price database engine , MSDE may meet your needs . MSDE if a free sql server database desktop engine , you can download it from microsoft.com .Just like sql server enterprise edition , msde also provide an easy way to back up your important data , it supports full and diffenentail backup , for access db , you have to copy the whole file for each time you back it up .

--David
databk.com