Database capacity

Hi,

I’m working in a DWH project the source system have data from passed 30 years and the total number of records from the source system is 10 million.

I’m need to do the estimation for the data base for the up coming 10 years of data.

As I can predict that the 30 year of data is 10 million then expected data for 10 years would not be more then 3 million.

The ETL tool I’m using is SQL Server Integration services and the reporting tool is Business Objects.

Can any one guide me on the implementation plan, meanmemory taken for ETL jobs (As SSIS use large amount of memory) database and business objects.

Sheikh

A good place to start is this link http://www.dmreview.com/article_sub.cfm?articleId=1048082

In talks in general about Capacity Planning.

Some basic things I would say -- almost a must have for a Datawarehouse

1) 64 Bit Environment -- you can use more than 4 GB of Memory in here (well you can use more than 4 GB in a 32 bit -- but 64 bit is better, especially if you can afford it).

2) Min of 8 GB of RAM. (considering you do not have more than 15 Million Rows).

3) Obviously -- enough Hard DIsk space for your DW.

4) A Good Disk Subsytem.

But a lot depends on your usage pattern and what you are trying to achieve. Ultimately it will all come down to cost vs benefit.

|||Thank you for the helpfull post.

Database Backup And Transaction Log - SQL Server 2000

I would like to find the cleanest way to do a backup and not have to backup
the log.
I have a number of DTS jobs that run as batch - loads data into SQL Server.
I know for fact that at a certain time after the batch, no one is in the
database and no jobs are running that affect the database of system
databases. I was thinking of just executing a CHECKPOINT to force any
completed records to disc, backing up the database, and then truncating the
log. Is this the correct order? I became when I saw an article that
did a backup, then checkpoint and then truncate log - in that condition the
backup would never cover the CHECKPOINTED data.
Any suggestions would be helpful. Database has full recovery mode.You will have to backup the log - or take the risk of going with the Simple
recovery model.
If you like, you can backup the log just prior to the start of the DTS jobs.
This will ensure that you have as much space as possible during the DTS run.
If your packages load a lot of data, you can backup the log more frequently
during the run than you would at other times. After the DTS run is done,
that would be a good time to schedule your full backup.
Another thing you may want to consider is to switch your recovery model to
Bulk-logged during the running of the DTS package(s).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"MG" <MG@.discussions.microsoft.com> wrote in message
news:5A388DAD-5C6A-414C-946E-DEBA3922A769@.microsoft.com...
I would like to find the cleanest way to do a backup and not have to backup
the log.
I have a number of DTS jobs that run as batch - loads data into SQL Server.
I know for fact that at a certain time after the batch, no one is in the
database and no jobs are running that affect the database of system
databases. I was thinking of just executing a CHECKPOINT to force any
completed records to disc, backing up the database, and then truncating the
log. Is this the correct order? I became when I saw an article that
did a backup, then checkpoint and then truncate log - in that condition the
backup would never cover the CHECKPOINTED data.
Any suggestions would be helpful. Database has full recovery mode.|||
When you backup the database and the log then you will be able to shrink the
log file. If you do not backup the log then you cannot shrink therefore the
log file could get very big. I have a feeling you already know that.
"MG" <MG@.discussions.microsoft.com> wrote in message
news:5A388DAD-5C6A-414C-946E-DEBA3922A769@.microsoft.com...
>I would like to find the cleanest way to do a backup and not have to backup
> the log.
> I have a number of DTS jobs that run as batch - loads data into SQL
> Server.
> I know for fact that at a certain time after the batch, no one is in the
> database and no jobs are running that affect the database of system
> databases. I was thinking of just executing a CHECKPOINT to force any
> completed records to disc, backing up the database, and then truncating
> the
> log. Is this the correct order? I became when I saw an article
> that
> did a backup, then checkpoint and then truncate log - in that condition
> the
> backup would never cover the CHECKPOINTED data.
> Any suggestions would be helpful. Database has full recovery mode.|||Tom, Thanks for the info but I am not sure why my concept would not work.
Simply CHECKPOINT the trans log to get what is left in the trans log to the
mdf, truncate the log, then do a database backup (without backingup the tran
s
log). Why would this not work or be affective?
"Tom Moreau" wrote:

> You will have to backup the log - or take the risk of going with the Simpl
e
> recovery model.
> If you like, you can backup the log just prior to the start of the DTS job
s.
> This will ensure that you have as much space as possible during the DTS ru
n.
> If your packages load a lot of data, you can backup the log more frequentl
y
> during the run than you would at other times. After the DTS run is done,
> that would be a good time to schedule your full backup.
> Another thing you may want to consider is to switch your recovery model to
> Bulk-logged during the running of the DTS package(s).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "MG" <MG@.discussions.microsoft.com> wrote in message
> news:5A388DAD-5C6A-414C-946E-DEBA3922A769@.microsoft.com...
> I would like to find the cleanest way to do a backup and not have to backu
p
> the log.
> I have a number of DTS jobs that run as batch - loads data into SQL Server
.
> I know for fact that at a certain time after the batch, no one is in the
> database and no jobs are running that affect the database of system
> databases. I was thinking of just executing a CHECKPOINT to force any
> completed records to disc, backing up the database, and then truncating th
e
> log. Is this the correct order? I became when I saw an article th
at
> did a backup, then checkpoint and then truncate log - in that condition th
e
> backup would never cover the CHECKPOINTED data.
> Any suggestions would be helpful. Database has full recovery mode.
>|||Executing the CHECKPOINT command will not in any way affect what will be inc
luded on your backups.
(SQL Server will do an internal CHECKPOINT with the backup execution anyhow,
but that is beside the
point.) Backup database will include all the data in a consistent state. Wha
t haven't been committed
will be restored when you do RESTORE DATABASE.
Also, "truncating the log" isn't considered good practice. Either you do reg
ular log backups, and
the log file will be emptied each time you do a log backup. By truncating th
e log, you break that
chain of log backups. Or, you don't do log backups, and all you have to do i
s to have the database
in simple recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MG" <MG@.discussions.microsoft.com> wrote in message
news:94AD791B-2749-4B4E-BCF6-82A32073D8A0@.microsoft.com...
> Tom, Thanks for the info but I am not sure why my concept would not work.
> Simply CHECKPOINT the trans log to get what is left in the trans log to th
e
> mdf, truncate the log, then do a database backup (without backingup the tr
ans
> log). Why would this not work or be affective?
> "Tom Moreau" wrote:
>|||Tibor,
Thanks for clearing that up. I did not know that when a backup occurs that
SQL does a checkpoint. Now it all fits. Is the Checkpoint done with the
database backup or the trans log backup?
"Tibor Karaszi" wrote:

> Executing the CHECKPOINT command will not in any way affect what will be i
ncluded on your backups.
> (SQL Server will do an internal CHECKPOINT with the backup execution anyho
w, but that is beside the
> point.) Backup database will include all the data in a consistent state. W
hat haven't been committed
> will be restored when you do RESTORE DATABASE.
> Also, "truncating the log" isn't considered good practice. Either you do r
egular log backups, and
> the log file will be emptied each time you do a log backup. By truncating
the log, you break that
> chain of log backups. Or, you don't do log backups, and all you have to do
is to have the database
> in simple recovery mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MG" <MG@.discussions.microsoft.com> wrote in message
> news:94AD791B-2749-4B4E-BCF6-82A32073D8A0@.microsoft.com...
>
>|||I know for sure it is done with a database backup. Whether it is performed w
ith a log backup, I
don't know, but to me it doesn't matter as all I need to know is that the re
levant log records are
recorded in the backup media. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MG" <MG@.discussions.microsoft.com> wrote in message
news:6D13A79A-51F0-4C33-95C2-C992B0ADE61E@.microsoft.com...
> Tibor,
> Thanks for clearing that up. I did not know that when a backup occurs that
> SQL does a checkpoint. Now it all fits. Is the Checkpoint done with the
> database backup or the trans log backup?
> "Tibor Karaszi" wrote:
>|||Tibor,
I agree. thanks
"Tibor Karaszi" wrote:

> I know for sure it is done with a database backup. Whether it is performed
with a log backup, I
> don't know, but to me it doesn't matter as all I need to know is that the
relevant log records are
> recorded in the backup media. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MG" <MG@.discussions.microsoft.com> wrote in message
> news:6D13A79A-51F0-4C33-95C2-C992B0ADE61E@.microsoft.com...
>
>sql

Data/Preview windows not showing same data return

When I test my query in the "Data" window (tab), I get a healthy number of
rows returned. When I test under the "Preview" window (tab), I get a tiny
fraction of rows returned. Can someone tell why this is? Perhaps, something
in the "Layout" that I am missing? I'm not even sure how the data I am
getting in the "Preview" window is unique compared to the other data I did
not get.
Thanks in advance for any clues!Here is what might be happening. The preview tab caches data. If the
parameter is not changed it re-uses the data. Look where your .rdl file is
and you should see files with a .data extension. Either delete this file to
force it to hit the database again or just run your report with a different
parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lu" <Lu@.discussions.microsoft.com> wrote in message
news:E1372BDD-F320-4687-9390-F703136400FF@.microsoft.com...
> When I test my query in the "Data" window (tab), I get a healthy number of
> rows returned. When I test under the "Preview" window (tab), I get a tiny
> fraction of rows returned. Can someone tell why this is? Perhaps,
> something
> in the "Layout" that I am missing? I'm not even sure how the data I am
> getting in the "Preview" window is unique compared to the other data I did
> not get.
> Thanks in advance for any clues!|||Thanks, Bruce. I flushed the old data, but this did not resolve the issue.
I forgot to mention that I deployed as well thinking it may have been cached
data. However, the problem also persists on production. I have the
parameters the same on both test preview and the raw query (data tab) and
even in the SQL Srvr Mgmt Studio...the preview and production windows only
retrieve 3 rows of data as opposed to the expected 3,000+ rows of data. It
should also be noted that something must've changed that I didn't catch
because the query was pulling more info than it is now...not all of it, but
was still pulling more than now.
"Bruce L-C [MVP]" wrote:
> Here is what might be happening. The preview tab caches data. If the
> parameter is not changed it re-uses the data. Look where your .rdl file is
> and you should see files with a .data extension. Either delete this file to
> force it to hit the database again or just run your report with a different
> parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lu" <Lu@.discussions.microsoft.com> wrote in message
> news:E1372BDD-F320-4687-9390-F703136400FF@.microsoft.com...
> > When I test my query in the "Data" window (tab), I get a healthy number of
> > rows returned. When I test under the "Preview" window (tab), I get a tiny
> > fraction of rows returned. Can someone tell why this is? Perhaps,
> > something
> > in the "Layout" that I am missing? I'm not even sure how the data I am
> > getting in the "Preview" window is unique compared to the other data I did
> > not get.
> > Thanks in advance for any clues!
>
>

data types in SQL Server

I'm new to SQL Server, but could someone explain how to set up a field with an auto number/increment that can be used within an Access DB?Go to Books OnLine. Lookup IDENTITY. Read. Create DDL. Try it, you'll like it :) .|||Do you have SQL Server Client Tools Installed?

Do you know what Books Online is?

How about Enterprise Manager or Query Analyzer?

Do you know what DDL is?|||In reply to your questions:

Yes :)
No :confused:
Yes :)
No :confused:

I've found all that and read about it but that's what I've already done. What I wanted to do was set up a system in Access using forms etc but link the tables to SQL Server instead.
Usually when you create the tables in Access and set up an autonumber field, create a form, run it, when you change part of the form it automatically displays the number when you add a record, I take it when doing it through SQL Server it doesn't do that?|||First, I recommend that you create and Access Data Project rather than an Access MDB with linked tables, if you have not already taken this route.

Second, why are you displaying the auto-generated ID on your form anyway? Identity values are surrogate keys, and on principle should not be exposed to the users.

Third, create your auto-incrementing ID value in your table using Enterprise Manager. The identity value is not assigned, however, until the record is saved, so it will not be able to display it on your form until the transaction is committed.

Data Type > Save various object types

Hello,

I am working on a .NET project and I would like to save some LOCALIZED content in an SQL 2005 database.

This content might be a string, a number or even a class with its properties values defined so I can retrieve it later.

I will not need search or any complex operation just the following:
1. Add content to the database
2. Remove content from the database by contentId
3. Get content from the database by contentId

Anyway, is this possible?

What SQL 2005 datatype should I use to accomplish this?

Thanks,
Miguel

I suppose the only one which will work for you isvarbinary(max) I suppose

Thanks

data transformation

I have data that is listed in a number of formats (nvchar).
It needs to look like 000000-00-0 or N/A
It looks like any combination
999999999
99-99-9
9999 99 9
and similar
And ideas?
If you are talking about formatting the number for display, that is often
left to the front end...
However you can create a UDF which accepts the value as input, formats it
and returns it as output...
If I were writing such a function I would probably check the input for
hyphens, spaces or neither and format accordingly..
In the Books On Line, start by searching for and reading about all of the
string functions in SQL...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"bhoweaz" <bhoweaz@.discussions.microsoft.com> wrote in message
news:2BA9CE10-B0B7-4EB5-A0CA-53C5A82CF070@.microsoft.com...
> I have data that is listed in a number of formats (nvchar).
> It needs to look like 000000-00-0 or N/A
> It looks like any combination
> 999999999
> 99-99-9
> 9999 99 9
> and similar
> And ideas?

data transformation

I have data that is listed in a number of formats (nvchar).
It needs to look like 000000-00-0 or N/A
It looks like any combination
999999999
99-99-9
9999 99 9
and similar
And ideas?If you are talking about formatting the number for display, that is often
left to the front end...
However you can create a UDF which accepts the value as input, formats it
and returns it as output...
If I were writing such a function I would probably check the input for
hyphens, spaces or neither and format accordingly..
In the Books On Line, start by searching for and reading about all of the
string functions in SQL...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"bhoweaz" <bhoweaz@.discussions.microsoft.com> wrote in message
news:2BA9CE10-B0B7-4EB5-A0CA-53C5A82CF070@.microsoft.com...
> I have data that is listed in a number of formats (nvchar).
> It needs to look like 000000-00-0 or N/A
> It looks like any combination
> 999999999
> 99-99-9
> 9999 99 9
> and similar
> And ideas?