August 15, 2014 at 4:44 pm
curious_sqldba (8/15/2014)
So you are saying point in time recovery for a partitioned db will take longer time as it will have hardened transactions for all the partitions.
No, I said nothing of the sort.
Firstly, there's some terminology problems here. There's no such thing as a partitioned database. Tables and indexes have partitions.
You're talking about scaling out a workload across multiple servers. Manually splitting data among multiple databases and spreading those databases across servers. Firstly, that's only done at the highest level of workload. Even Stack Overflow which runs entirely on SQL Server (and does 50 000 transactions/second sustained for hours) uses a single server.
Second, it's incredibly hard to architect and design. Incredibly hard. Because there are so many data synchronisation, recovery, integrity concerns that just don't come up when everything's in a single database.
Partitioning is something you do to tables to allow easier maintenance (like index rebuild) and data archiving/deletion.
In your case, the workload is small and so any decisions about multiple databases or one database must come from the business requirements. You need to work through the business requirements and see whether there's a need to split clients data into separate databases (most common reason is due to needing to restore them independently), or whether they can all go in one database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2014 at 4:57 pm
It seems that you are in a position where you are looking to architect a solution that may be out of your breadth.
I'd recommend taking a step back and looking at the overall requirements (like Denny said). If it seems that the requirements are still in need of a solution to architect partitioning or multiple databases (e.g. one database per client), then bring in a consultant to help iron out the details and the technology solution.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 19, 2014 at 7:11 am
Are there any other reasons except maintenance overhead if I split my partitions to separate file group? Reason I say that is if I have multiple file groups I can just backup that one file group and restore on Dev server for troubleshooting purposes.
August 19, 2014 at 7:44 am
curious_sqldba (8/19/2014)
Reason I say that is if I have multiple file groups I can just backup that one file group and restore on Dev server for troubleshooting purposes.
No, you can't.
To restore on dev, you'd have to first restore the Primary filegroup, that always has to be the start of the restore, then restore the filegroup you want, then restore all log backups taken over the interval between when the primary filegroup was backed up and when the other filegroup was backed up.
You also have to carefully design the database so that the filegroups are mostly independent, so that you can restore only part of the database and have it usable, not need tables in other filegroups. That tends to be a fair amount of analysis and design work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2014 at 8:50 am
curious_sqldba (8/19/2014)
Are there any other reasons except maintenance overhead if I split my partitions to separate file group? Reason I say that is if I have multiple file groups I can just backup that one file group and restore on Dev server for troubleshooting purposes.
Gail already answered you in regards to the maintenance. As far as performance impact of splitting into different filegroups an files, it depends on your hardware and system configuration, the types of disks you have (SSD?) and other parameters. You need to discuss this with your IT about the benefits and drawbacks of splitting into different disks that R/W at different speeds.
August 19, 2014 at 9:09 am
N_Muller (8/19/2014)
curious_sqldba (8/19/2014)
Are there any other reasons except maintenance overhead if I split my partitions to separate file group? Reason I say that is if I have multiple file groups I can just backup that one file group and restore on Dev server for troubleshooting purposes.Gail already answered you in regards to the maintenance. As far as performance impact of splitting into different filegroups an files, it depends on your hardware and system configuration, the types of disks you have (SSD?) and other parameters. You need to discuss this with your IT about the benefits and drawbacks of splitting into different disks that R/W at different speeds.
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply