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

Hi,

I am using one database to store some information, which is now in my local system.Now i want to create a copy of the database, which is in my system to another system (including the data in the table's and the stored procedures i have created).

Is this possible. if yes please help

Thanks in advance....

If you want to use the database with data then you can RESTORE this A database with a new name.

If not you can script this database with all objects and use the same script to create another database with new name.

Refer to books online for RESTORE and SCRIPT for more information.

Database BackUp

Can we save Database Backup at our local system instead of at server machine?

Quote:

Originally Posted by sajithamol

Can we save Database Backup at our local system instead of at server machine?

I recomment design SSIS package and specify location of new backup file by input parameters or set path constantly.sql

Database Audit

Hi,

We are currently porting our POINT OF SALE system to SQL Server 2005. In our existing software we log database changes (inserts, amendments and deletions) to a flat acsii files so that these files can be FTP'd to remote sites (largest number of sites 100+) and processed on to replicate the data.

I have looked at REPLICATION in MSSQL 2005 but we need two way replication and additional processing. Therefore I dont think we will be able to use this feature.

Therefore what I wanted to do was to try and setup an automated way of capturing database changes to all tables within the database and log these changes to XML to be shipped out to remote sites. Unfortunatly I can only find TABLE TRIGGERS which would require creating 100's of triggers as we have 100's of tables.

Is there anyway of setting up MSSQL server to automatically do something like this... I was looking to see if there was a DATABASE TRIGGER which could perform this action but I cant see anything...

Can anyone advise or is there a simpler way of doing this ?

Ray

I think that Log Shipping may be a useful solution in your situation.

Refer to Books Online, and the topic: Log Shipping.

Database access log

SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish

database

We are a couple of student on the area of system development. We are
trying to make a database system that will treat the manipulation and
updating of sounds. The input/output of it, has to be sounds in
graphic, acoustic, form etc. Are there any database systems that
support these kinds of data?
Hi
SQL Server 2005 does have BLOB datatypes
http://msdn2.microsoft.com/en-US/library/ms131277.aspx
http://msdn2.microsoft.com/en-us/library/ms130896.aspx and a XML datatype
http://msdn2.microsoft.com/en-us/library/ms131375.aspx that could be used for
holding this type of data; although you may wish to store these files on the
O/S see
http://databases.aspfaq.com/database...ilesystem.html
John
"Farhad" wrote:

> We are a couple of student on the area of system development. We are
> trying to make a database system that will treat the manipulation and
> updating of sounds. The input/output of it, has to be sounds in
> graphic, acoustic, form etc. Are there any database systems that
> support these kinds of data?
>

database

We are a couple of student on the area of system development. We are
trying to make a database system that will treat the manipulation and
updating of sounds. The input/output of it, has to be sounds in
graphic, acoustic, form etc. Are there any database systems that
support these kinds of data?Hi
SQL Server 2005 does have BLOB datatypes
http://msdn2.microsoft.com/en-US/library/ms131277.aspx
http://msdn2.microsoft.com/en-us/library/ms130896.aspx and a XML datatype
http://msdn2.microsoft.com/en-us/library/ms131375.aspx that could be used for
holding this type of data; although you may wish to store these files on the
O/S see
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
John
"Farhad" wrote:
> We are a couple of student on the area of system development. We are
> trying to make a database system that will treat the manipulation and
> updating of sounds. The input/output of it, has to be sounds in
> graphic, acoustic, form etc. Are there any database systems that
> support these kinds of data?
>

database

We are a couple of student on the area of system development. We are
trying to make a database system that will treat the manipulation and
updating of sounds. The input/output of it, has to be sounds in
graphic, acoustic, form etc. Are there any database systems that
support these kinds of data?Hi
SQL Server 2005 does have BLOB datatypes
http://msdn2.microsoft.com/en-US/library/ms131277.aspx
http://msdn2.microsoft.com/en-us/library/ms130896.aspx and a XML datatype
http://msdn2.microsoft.com/en-us/library/ms131375.aspx that could be used fo
r
holding this type of data; although you may wish to store these files on the
O/S see
http://databases.aspfaq.com/databas...filesystem.html
John
"Farhad" wrote:

> We are a couple of student on the area of system development. We are
> trying to make a database system that will treat the manipulation and
> updating of sounds. The input/output of it, has to be sounds in
> graphic, acoustic, form etc. Are there any database systems that
> support these kinds of data?
>

Data Type ?

Hi Forum, Im new to SQL db and am receiving this error when updating Detailview.

System.Data.SqlClient.SqlException: The data types text and nvarchar are incompatible in the equal to operator.

DB Table PK Customer_ID is set Int also Mobile and PIN columns, all others are Text. Could it be that a combination of text and a number ie 7a 3 MyStreet, be cause?

Detailview Update Parameters as below

<asp:ParameterName="Mobile"Type="Int32"/>

<asp:ParameterName="PIN"Type="Int32"/>

<asp:ParameterName="Street"Type="String"/>

<asp:ParameterName="original_Customer_ID"Type="Int32"/>

Select fills Detailview with Table values OK, its on UPDATE things go wrong! much thanks Paul

Hi pl,

Your trouble is SQL level. You would have to change Text datatype to NVarchar datatype in your columns.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

Don't use the text data type. Change them to varchar(8000) if you must, or use a more realistic number for it's maximum length. If you are using SQL Server 2005 or SQL Express, you can also use varchar(max), which is pretty close to the same thing as text with quite a few less restrictions.

As a side note, it's the sqldatasource that has the problem. Although we don't really need to see it, we can guess what your update statement looks like. It's trying to compare a text field to an original value, and you can't do that. Using text columns for comparision within a WHERE clause isn't allowed.

|||

Thanks for both your replies, changing to column to varchar did the trick!

Something else you could help me with is SQL connection string. Im used to using Access DB, OLEDB

publicString str;

publicstring strAccessConn ="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\\inetpub\\vhosts\\mtaxi.co.nz\\httpdocs\\data\\mtaxidb1.mdb";

OleDbConnection myAccessConn =newOleDbConnection(strAccessConn);

How to change this toSystem.Data.SqlClient ?

I have connection in web.config

connectionStrings>

<addname="MT_ConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MTaxidb1.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

SqlConnection myConnection = new SqlConnection("****THIS STRING IM UNSURE OF****");

Really appreciate advice Paul

|||

I'm going to be close, but I don't have the exact code in front of me, but what you want is something similiar to:

SqlConnection myConnection=new SqlConnection(ConfigurationManager.ConnectionStrings("MT_ConnectionString").ConnectionString);

Or you could use this (but obviously it's not configurable then):
SqlConnection myConnection = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MTaxidb1.mdf;Integrated Security=True;User Instance=True");

|||

Thanks Motely, This has all been good information! helped me out big time cheers P

string incase anyone is interested

strSqlConn = System.Configuration.ConfigurationManager.ConnectionStrings["MT_ConnectionString"].ToString();

Data Transformation in Replication

I have a transactional replication set up from one system to another.
Subscriber has only read mode on data (Uni-directional Replication). I need
to do lots of transformation in the data while replication from Publisher to
Subcriber Like adding the same record in the other table as well or do some
denormalization etc.
Which route should I choose?
1. Modify Replicaiton procedures to have this business rule in-built.
2. Should I create indexed views and other tables on reporting server.
3. Should I create trigger on the reporting server and have the replication
procedure only do the insert and trigger do rest of the business rules
implementaion.
4. Can reporting services be used for such a transformation.
5. Should I have DTS as a part of replication.
-Nitin
1. Yes, these procs will be whacked the next time you do a reinitialization.
Make sure you uncheck the Create the stored procedures during the initial
synchronization of subscriptions option. This is possibly the preferred way
to go.
2. This is another option. However Indexed Views may not be able to do
everything you need. Indexed Views are less complex to set up than custom
stored procedures. Indexed Views are best used on EE, as opposed to standard
as their indexes won't be included in the query plans query optimizer
generates on standard.
3.No, No, No, No, No. You will find that triggers will slow down the
performance of the replication stored procedures. Your latency will increase
many fold.
4. I believe so, however, replication processes may be better tuned for this
this. I think this will be the case only if you replicate the execution of
stored procedures.
5. No, for performance reasons. Use DTS when you have to modify the data en
route to heterogeneous subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nitin" <Nitin@.discussions.microsoft.com> wrote in message
news:1C92DDBA-5FC5-4910-B022-A87A7C75466D@.microsoft.com...
> I have a transactional replication set up from one system to another.
> Subscriber has only read mode on data (Uni-directional Replication). I
need
> to do lots of transformation in the data while replication from Publisher
to
> Subcriber Like adding the same record in the other table as well or do
some
> denormalization etc.
> Which route should I choose?
> 1. Modify Replicaiton procedures to have this business rule in-built.
> 2. Should I create indexed views and other tables on reporting server.
> 3. Should I create trigger on the reporting server and have the
replication
> procedure only do the insert and trigger do rest of the business rules
> implementaion.
> 4. Can reporting services be used for such a transformation.
> 5. Should I have DTS as a part of replication.
> -Nitin