August 12, 2010 at 6:10 pm
I have a Large database (near 1 TB) , I have a table for users list (username,pass,...) and some tables for users data( one of these tables has 100 milion rows). but there is not any relation between users data. I think that i can split database to some databases. for example make one database for users (a*,b*,c*,d*) and one database for (e*,f*,...). and one database for userslist. all databases have equal I/O and all of them have equal transactions.
also I need that host some of these databases in one hardrive for some months.
Is it good solution? or I have one database but I split data in multiple tables ( one table for users a*, one table for users b*,...)
in multiple databases solution I can move database to new server without stopping userslist database or other users database.( i can deattach database user a*,b* but database f* is available)
which way is better?
August 13, 2010 at 1:39 pm
if you're using enterprise edition or higher, why not take a look at partitioning horizontally your table?
This is a powerful functionality.
August 13, 2010 at 9:01 pm
Thanks youMegistal
I have some problems and suggection solutions.
1.My harddisk is limited and database files over 1 TB! (suggection solution: using enterprise strorge)
2.the database files is very large and tbales have too many rows.I need 1 hour for Reindex table on UserId ( my indexes is on UserId(Clustered) and ArticleId and ArticleDate.( suggection solution:using partion tables or split tables )
3.Moving data files need serveral hours( for example when server has problem and I need to move data to new server) and when I move data files ,all users can not using application.
4.run dbcc checkdb or check table need serveral hours
5.when I/O or harddisk has problem. database is damaged or suspected and i need serveral hours or two days to repair it( using single mode and run dbcc checkdb REPAIR)
when using multiple databases I can add new server for host new users data or transfer some part of data to new server , also I read that every database on one server ( and every file group in single database) using new thread and it increate performance .
It is a comparison, which solution is better. using a single database with enterprise strorage or using
multiple databases in one or serveal servers?
August 16, 2010 at 6:22 am
Hello adnani,
1: If the databases requires additional space there's not much you can do.
It seems that this is temporary, two things on that, if it's temporary you should be able to require assistance for that limited time (additional space for instance) but beware, most of the time (well from my experience) temporary solutions become permanent...
Shrinking databases and logs but this is not recommended because this will increase fragmentation which will lead to lower performance.
Use the filtering index to remove unused part of indexes you don't need
http://www.sql-server-performance.com/articles/dba/Filtered_Indexes_in_SQL_Server_2008_p1.aspx
Review archiving methods.
Review with BI users which data can be deleted
If some unused data are on a specific read-only filegroup, don't restore that filegroup.
2: Check filtered index.
Also check how the indexes are optimize, check if it's mandatory to rebuild them instead of reorganizing them
For clustered, if they key is incremental (identity for instance), maintenance on that index should be very minimal
3:Setup log shipping while doing so?
Take a look at this article to minimize users downtime
http://www.mssqltips.com/tip.asp?tip=2073&home
4: This happen, you need a maintenance window no matter what they tell you. Otherwise the performance will go down by the day.
5:hummm REPAIR and allow_data_loss I presume? This should be a last resort option.
Why not restoring the data page which has failed only?
Check the drive if it's a physical issue with it.
If it's a non-clustered index, you can drop it (yeah it will take time to rebuild) and recreate it.
Don't forget to send a notice telling the application will be slower for a time.
It is a comparison, which solution is better. using a single database with enterprise strorage or using
multiple databases in one or serveal servers?
For that I cannot say, there's a lot to take into account. Enterprise policies, server performance, consolidation, high availability.
This is something you should review with the company as the "best solution" is the solution which will best suit the company.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply