Database structure advice 1

[micang]

Hi ALL,

We currently have 3 databases on a desktop running SQL 2000. We now have purchsed 2 servers (I have been told is very well speced) and will be running SQL 2005.

We are new to SQL and all we really have are large tables (45 million rows) on each database which we query out and cleanse data using QA and EM. We have a few smaller tables we use to link to the larger tables to "decode".

I am not too sure how to go about setting up the 2 new servers. We are using SQL purely because of the amount of data, otherwise we could do all of it in Access.

There is no new data/daily input , so basically the tables are static, only changes occur when we do soem cleansing on the data and change it there and then, the data is only accessed internally by 2 people. Because of having 2 servers to our disposal, I am not sure if it would make sense having a "reporting" server which would be used purely to extract data then a "development" server used to do cleansing etc?

Also, the 3 databases for example are: Used Cars, cars on the road, New cars. I think we want continue having these three databases seperatly on the new servers (Reporting and development, if we go down this route). One question I am confused about is that in all three databases there are "decode" tables that are exactly the same. I don't think this is ideal as when we have to change or add data to one of these tables, we have to do it for the other 2 aswell.

So maybe I may be doing it all wrong and just put all the tables from the 3 databases into 1?

Sorry for the vagueness and I know this is little information, but if anyone can guide me in the sort of right direction, will be very appreciated.

Many thanks.

Michael

 

[ptheriault]

You have 3 databases that contain 3 tables in each database?
You should put them all in the same database and remove anything that is duplicated.

I'm not sure I really understand your question here. Do you need advice on how to install SQL? We would need to know how you have configured your hardware.

>>There is no new data/daily input
How does data get into these databases?

>> I am not sure if it would make sense having a "reporting" server which would be used purely to extract data then a "development" server used to do cleansing etc?

I think making one server just for reporting is overkill here. You have 2 people using this database. I think it's safe to say you are not going to run into many contention problems.

I think if you could provide some more details here we could help you a little better.



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Hi Paul,

Thank you for the input thus far. For the installing of the current desktop (SQL2000) I just installed SQL 2000 using defaults and then applied SP4. Now on the new actual server machines we pretty much going to install SQL2005 the same way.

As for the current databases: Dbase 1 (cars_on_the_road) has about 8 tables, 2 are around 35 million rows each (one for 2005 data and one for 2006), the remainder are small, ranging from 50 to a few thousand rows. For the main tables one row represents one vehicle record.

The two big tables store all the data then we use the remainder of the tables to link up to them to decode information. (Example link post code to post code to get region)

Dbase 2 and 3 follow very simillar setups, just that they are for different data sets. And some of the smaller tables are the same across each three dbases.

The nature of the data is that it's not "live", by that I mean once we put in the tables with the data, they remain static and don't change. We get this data from other sources.

For Db 1 this happens once year, inserting a new table with the new years data.

For Db 2 and 3 we insert new tables every quarter (3 months).

Myself and the other user are new to SQL and basically have to teach ourselves (And you guys have taught me 99% of what I know). The major purpose of this is to create reports for clients, so we basically query out extract using QA and then exporting them to Access and manupulate them further in that enviroment. (I have tried to adp from Access to the database, but most of the queries time out).


We do have another database that holds another big table (50 odd million rows), which we run queries to cleanse the data.

Sorry for the long post and vagueness! And I am not sure what information to give (like sample data in the table etc) and I also don't want to waste your guys time!

Michael

 

[ptheriault]

As for the current databases: Dbase 1 (cars_on_the_road) has about 8 tables, 2 are around 35 million rows each (one for 2005 data and one for 2006), the remainder are small, ranging from 50 to a few thousand rows. For the main tables one row represents one vehicle record.

I would combine all your data into one database. You may want to combine tables to. Just add a year column. You don't want a table for each data set. That is what querries are for.

I would set up your servers so you have one that is production and one that is dev. Not having a lot of experience with SQL it will be nice to have server you can mess around with.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Hi Paul,

Thanks

I haven't got specs for the the new server, but apparently it's well speced and powerful (it's the same server spec as one of our supplies use). My question is, would it be OK then to have one table of 70 odd million rows?

Thanks so much for your input and time.

Michael

 

[ptheriault]

>>would it be OK then to have one table of 70 odd million rows?

That is fairly common. You wont have any problems as long as you have it properlly indexed. We can help you with that once you set it up.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Excellent, thanks so much Paul, I definelty will need help indexing it, currently I have set up indexes on the desktop server we are using, but I don't think I have them set up as efficiently as they could be.

To prepare myself, what kind of info would be required for help on setting up indexes?

Michael

 

[ptheriault]

Here are some very useful links. That can help you with some of the things you will need to know.

http://www.sqlusa.com/bestpractices2005/

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx

http://www.sql-server-performance.com/articles_performance.asp

http://www.informit.com/articles/article.asp?p=28285&rl=1

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Excellent thanks.

Once last question I should have asked first! Currently we use t-sql statements to extract data on SQL 2000, these should work in 2005 I assume?

They simple queries, select, inner joins and where mainly.

We also do a lot of crosstabing and I have an ugly piece of code that I use in SQL, does the trick for our purposes though. I believe 2005 has a PIVOT function that I may be able to utilise.

Paul, a star for your patience and time.

Michael

 

[ptheriault]

Michael,
I'm glad I could help. You T-SQL should be fine in 2005 and yes 2005 has a PIVOT function. I really haven't messed with it though.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

You really have, I feel a star is not thanks enough.

It's kind of frustrating, I feel I am at a stage I know I could do things better, but don't have the skills to impliment them! (I should make this my signature!)

Michael