Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kensai
Posting Yak Master
172 Posts |
 Posted - 2008-05-14 : 07:52:39
|
| I'm currently developing an ASP.NET site with SQL Server 2005 Standard and I'd like to ask a question about the future of the database. It needs to have continuity and performance. I'm thinking about doing replication or mirroring for continuity and table partitioning for performance. I admit I've never done any of those before and I'll learn about them but they're not needed at this time. The question is, I'm currently designing the database and do I have to anything for consideration for those things I'm thinking of implementing later? For example, I'm using Identity in my tables but I've heard about identity crisis using replication with identity columns, therefore I'm thinking of using Guid's but now I fear the Guid column index itself will be the slowdown factor in the first place.Any suggestions to consider? I'd appreciate any opinions. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-14 : 09:33:51
|
| 1)Database mirroring is High availability option and Replication is warm-standy option (There is no automatic failover).2)Table partition is actually good for huge tables but if you think you have, you can increase performance with Table part. It is supported only in enterprise edition.3)If only you are doing two-way replication like (merge),then you will have issue with identity and it's not a good idea to have Guid's in index.4)For performance, understand normalization in full details while designing tables 5)Make sure you take full use of performance monitoring,SQL profiler,Database tuning advisor and Execution plan analysis before deploying to production. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-14 : 23:19:35
|
| There are several ways to address identity issue in two-way replication, like use odd numbers (or positive numbers, lower range numbers) in publisher and even numbers (or negative numbers, higher range numbers) in subscriber. |
|
|
|
kensai
Posting Yak Master
172 Posts |
Posted - 2008-05-15 : 03:51:46
|
| After your comments I'm thinking of doing mirroring and horizontal table partitioning. As for the mirroring, my main goal would be continue to make the site work if the main database fails. So if I do one-way async mirroring with high performance option, and the main database fails, will the mirror continue to work same as the main? If it does, can I sync it when the main database comes online? Does partitioning the table add any complexity to this process? As for the last, how much of this can be automated by SQL Server?Thanks for the all help, I appreciate it. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-15 : 08:55:05
|
| "So if I do one-way async mirroring with high performance option, and the main database fails, will the mirror continue to work same as the main?"Yes ,but you have to manually failover to secondary server.There is a possibility of data loss." Does partitioning the table add any complexity to this process? "You will gain performance with table partition if you tables are huge.It doesn't add complexity. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-15 : 22:07:44
|
| >> can I sync it when the main database comes online?Db mirroring handles that. |
|
|
|
|
|
|
|
|
Question about considering scaling while designing