May 14, 2008 at 5:52 am
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.
May 14, 2008 at 10:32 am
It is hard to say what you should be considering without knowing what you system will be doing.
On the Identity / GUID issue, there is now a sequential GUID function that will give you GUIDS in ascending sorted order - this eliminates the index issues GUID's had previously.
So, the arguments pretty much boil down to a GUID vs. an integer - which is reasonable small (saving you some disk space) and manageable if you have to re-type it. Either will probably perform reasonably well. I tend to use Identity columns unless I anticipate needing Merge replication or am using GUIDS to separate information based on user session.
May 14, 2008 at 11:13 am
I don't think partitioning requires much other than thinking about how you're partition later (dates, numbers, etc).
Replication is more granular and you have to be sure to replicate every table you need. For continuity, mirroring is better and if you can move to the SQL Client (2005 based), it can redirect automatically.
May 15, 2008 at 1:51 am
Michael Earl (5/14/2008)
It is hard to say what you should be considering without knowing what you system will be doing.
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.
May 15, 2008 at 5:45 am
You could use mirroring for DR, but my preference would be to use an Active/Passive cluster.
The option to use - including the option of using partitioning is going to heavily depend on what the site is for. A very high transaction E-Commerce site will have a very different set of requirements than a streaming media site. You may get better results for a question like yours if you give a more specific description of what you are trying to accomplish.
May 15, 2008 at 7:37 am
Michael Earl (5/15/2008)
You could use mirroring for DR, but my preference would be to use an Active/Passive cluster.The option to use - including the option of using partitioning is going to heavily depend on what the site is for. A very high transaction E-Commerce site will have a very different set of requirements than a streaming media site. You may get better results for a question like yours if you give a more specific description of what you are trying to accomplish.
It's not an e-commerce website. There'll be very low maybe zero amount of transactions will be used, I haven't decided of that yet. The user will select some data to create very simple reports. They'll have a quota of amount of data they can select. When they create report it'll be reduced of their quota. When they'll reach zero they have to buy more which will be done by the system administrator. Every they the admins will enter a few hundreds of rows of new data. This is all of it. We just want to be the site available all the time and have no data loss if possible so we don't have to deal with people saying "hey I was creating a report while the server went down, I want my quota back!" 🙂
May 15, 2008 at 3:02 pm
I don't work with multiple servers, but I heard of having different DB servers incrementing on different values.
eg..
db1 goes: 1,4,7.....
db2 goes: 2,5,8.....
db3 goes 3,6,9.....
Then you never have duplicate keys. But like I said, I don't have to worry about this, so I never played with it before. What is the 'propper' way of doing this?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply