Balancing the Data ,Replication ?

Hi,

I have two sets of data i.e two instances of SQL server in two different cities. Data entry happens at both the places.I need to balance the data in two servers i.e. I need to Synchronize the data.

Currently I am Exchanging the data between two sides and using a Buffer database to update the incremental infromation.(Running a SQL script).

I have GUID columns in my database. I am too new to replication.I wanted to know whether I can implement repliaction and what changes i need to make?(e.g. Replacing GUID columns with Identity columns).
Permanent connectivity between two databases is required or Replication can be performed whenever connected?

Best Regards
RohitCan two databases having GUID columns can be replicated or needs to be altered to Integer column

Balancing scalability with availability

We are beginning to design a new application with SQL Server 2005. Our current production environment is slated to be two SQL Server 2005 machines with the databases residing on an EMC SAN. We have requirements to both have automatic failover between servers for availability and also be able to balance the load over two hot servers for scalability.

Can anyone point me in the right direction for things I need to consider in order to be able to implement both of these requirements? Can I implement database mirroring (for failover) and transactional replication (for balancing) given the hardware configuration I'v mentioned? Is more information needed? Where should I turn next?

I am coming off a mainly Oracle background for the last ten or so years with a smattering of SQL Server mixed in. I've tried to hit the ground running on this project, but sometimes find myself hitting the wall running instead.

Thanks,

Larry

Automatic failover can be accomplished with Database Mirroring, and clustering. With both of these technologies the mirrored server or the failover node will be offline so you can't access the databases on these mirrored servers - hence they won't deliver load balancing. For load balancing you could use replication assuming all the DML occurs on one server. Replication will not provide client failover however.

-- Hilary Cotter

|||

That is not correct.

The instance hosting the mirror database is NOT offline. It is online and running otherwise transactions would not be able to reach it. The mirror database is inaccessible, because it is in a recovering state.

As far as clustering goes, the secondary node is also not offline. It is online and functioning, otherwise, there would be no way to failover to the node. There is only one copy of the database within a cluster, so you can't use the secondary node to execute anything.

The combination that is being asked for - automatic failover along with scalability is not possible. Failover clustering will automatically failover, but is not a scalability solution. Database Mirroring running in high availability operating mode will automatically failover, but is not a scalability solution. However, if your applications are not coded to intercept the disconnect and then reconnect to the cluster, it doesn't matter if the cluster automatically fails over, because only those applications forming new connections will find it. If your applications are not coded to utilize the new connection library in ADO.NET that shipped with VS 2005, then your applications are not going to automatically failover to the mirror when it gets promoted.

Replication is your closest bet to getting both fault tolerance as well as scalability. If your applications are built with a connection library that can detect that the server they are connected to is offline and then reform a connection to a secondary server and continue issuing transactions, then you can accomplish the basic goals. Your application then handles the automatic failover while the replication engine keeps everything synchronized to allow scalability. The basic options here are transactional with queued updating subscribers and merge. I would NOT configure a bi-directional or peer-to-peer architecture unless your are going to have full control over your applications and can guarantee that you can never have a conflict generated.

Balancing IO. File Groups vs Raid

I will be doing some performance testing on financial application next
month. Without going into a lot of details, I suspect I will have a
potential bottleneck when writing to the log file.

My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
rpm drives, with about 1GB of memory on the array for caching.

This is a benchmark environment, so I am not concerned about loosing
data. I am looking for a little guidance on using raid (0 or 10)
and/or file groups to spread IO to db objects (log file(s), data,
indexes, tempdb, etc). I have read about and played with file groups
enough to know that SQL server does some level of load balancing
across file, but am unclear it is in parallel or serialized.

Common wisdom seems to be to separate data, non-clustered index, logs,
and tempdb onto separate files, but I am unclear on how to make best
use of the high-speed disk array. I'd greatly appreciate opinions on
which would perform better; one file on a stripe set of N drives (raid
0 or 10), N files in a file group placed on N (non-striped) drives, or
a combination of the two? Is the answer the same for both log and data
(or index) files?

Thanks,
-Bernie"Bernie" <velivis@.ix.netcom.com> wrote in message
news:e707e6a.0312111018.475c199a@.posting.google.co m...
> I will be doing some performance testing on financial application next
> month. Without going into a lot of details, I suspect I will have a
> potential bottleneck when writing to the log file.
> My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
> DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
> rpm drives, with about 1GB of memory on the array for caching.
> This is a benchmark environment, so I am not concerned about loosing
> data. I am looking for a little guidance on using raid (0 or 10)
> and/or file groups to spread IO to db objects (log file(s), data,
> indexes, tempdb, etc). I have read about and played with file groups
> enough to know that SQL server does some level of load balancing
> across file, but am unclear it is in parallel or serialized.
> Common wisdom seems to be to separate data, non-clustered index, logs,
> and tempdb onto separate files, but I am unclear on how to make best
> use of the high-speed disk array. I'd greatly appreciate opinions on
> which would perform better; one file on a stripe set of N drives (raid
> 0 or 10), N files in a file group placed on N (non-striped) drives, or
> a combination of the two? Is the answer the same for both log and data
> (or index) files?

Can't answer all of those (partly I'm not fully awake. :)

But you absolutely want your logs on RAID 1 or RAID 10, NOT RAID 0.
Remember, RAID 0 has no redundancy. If you lose your transaction log, you
lose data. Or more specifically, you lose your ability to recover up to the
most recent point in time.

Ideally, I'd probably dedicate some LUN to the Data (this lun can comprise
of a RAID 5, RAID 10, etc, depending on needs, disk space, etc. (I prefer
if I have the space to use RAID 10), have another for TEMPDB (though I've
always cheated on this and put it on my DATA disk, and then a separate RAID
10 for logs.

The best reason I can see for separate filegroups is to separate out indices
to their own drive (we do this) or to break out very large tables so that
you can use some more advanced backup schemes.

That's my 2 cents at 1:00 AM.

> Thanks,
> -Bernie|||The main things are that the transaction log shouldn't be on the same
disk (or array) as the data, and neither should the non-clustered
indexes.

Here's a setup I like for a six-drive box:

3 disks in a RAID-5 array for the data.
2 disks using RAID-1 (mirroring) for the transaction log.
1 disk for the non-clustered indexes.

My theory is if the singleton goes bad, we just reconstruct the
indexes, no big deal.

With more than six drives you can get fancier, e.g. have two data
files on separate RAID-5 arrays such that tables that are typically
joined in queries are not on the same array.

On 11 Dec 2003 10:18:37 -0800, velivis@.ix.netcom.com (Bernie) wrote:

>I will be doing some performance testing on financial application next
>month. Without going into a lot of details, I suspect I will have a
>potential bottleneck when writing to the log file.
>My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
>DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
>rpm drives, with about 1GB of memory on the array for caching.
>This is a benchmark environment, so I am not concerned about loosing
>data. I am looking for a little guidance on using raid (0 or 10)
>and/or file groups to spread IO to db objects (log file(s), data,
>indexes, tempdb, etc). I have read about and played with file groups
>enough to know that SQL server does some level of load balancing
>across file, but am unclear it is in parallel or serialized.
>Common wisdom seems to be to separate data, non-clustered index, logs,
>and tempdb onto separate files, but I am unclear on how to make best
>use of the high-speed disk array. I'd greatly appreciate opinions on
>which would perform better; one file on a stripe set of N drives (raid
>0 or 10), N files in a file group placed on N (non-striped) drives, or
>a combination of the two? Is the answer the same for both log and data
>(or index) files?
>Thanks,
>-Bernie|||"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
news:nqnntvk42k1b5fjq2ui7f8fa0huttaps42@.4ax.com...
> The main things are that the transaction log shouldn't be on the same
> disk (or array) as the data, and neither should the non-clustered
> indexes.
> Here's a setup I like for a six-drive box:
> 3 disks in a RAID-5 array for the data.
> 2 disks using RAID-1 (mirroring) for the transaction log.
> 1 disk for the non-clustered indexes.

Personally I would not do this (single disk for non-clustered indices).
Mostly because in my environment (others of course will vary) loss of the
indices will effectively make the DB useless from a DB point of view.
> My theory is if the singleton goes bad, we just reconstruct the
> indexes, no big deal.
> With more than six drives you can get fancier, e.g. have two data
> files on separate RAID-5 arrays such that tables that are typically
> joined in queries are not on the same array.|||If it happens and inconveniences the users, we're that much closer to
a SAN. :)

On Mon, 15 Dec 2003 03:57:12 GMT, "Greg D. Moore \(Strider\)"
<mooregr@.greenms.com> wrote:

>"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
>news:nqnntvk42k1b5fjq2ui7f8fa0huttaps42@.4ax.com...
>> The main things are that the transaction log shouldn't be on the same
>> disk (or array) as the data, and neither should the non-clustered
>> indexes.
>>
>> Here's a setup I like for a six-drive box:
>>
>> 3 disks in a RAID-5 array for the data.
>> 2 disks using RAID-1 (mirroring) for the transaction log.
>> 1 disk for the non-clustered indexes.
>Personally I would not do this (single disk for non-clustered indices).
>Mostly because in my environment (others of course will vary) loss of the
>indices will effectively make the DB useless from a DB point of view.
>>
>> My theory is if the singleton goes bad, we just reconstruct the
>> indexes, no big deal.
>>
>> With more than six drives you can get fancier, e.g. have two data
>> files on separate RAID-5 arrays such that tables that are typically
>> joined in queries are not on the same array.|||"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
news:bcgttvkt2u15bnfmlr9m6d1a1u6f2256k0@.4ax.com...
> If it happens and inconveniences the users, we're that much closer to
> a SAN. :)

Now see, thre I like how you think. :-)|||:)

Actually your previous post made me realize that I should organize
something the tech guys can use to make the database quickly reusable
in the event the singleton fails and I'm not around. So thank you!
:)

I think maybe I will create an empty filegroup "EMERGENCY" on the RAID
5 array, and make a script to drop all the nonclustered indexes from
the filegroup that's on the singleton and then recreate them in
EMERGENCY. Whaddya think?

On Tue, 16 Dec 2003 15:01:43 GMT, "Greg D. Moore \(Strider\)"
<mooregr@.greenms.com> wrote:

>"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
>news:bcgttvkt2u15bnfmlr9m6d1a1u6f2256k0@.4ax.com...
>> If it happens and inconveniences the users, we're that much closer to
>> a SAN. :)
>>
>
>Now see, thre I like how you think. :-)

Balancing data between files

We are storing all our SQL 2000 databases on SAN LUNs, and one of our databases currently uses a single 40GB file which is approaching capacity. If we add further files using different LUNs, the data will start being added to these new files. My questions are these: if we were to add a number of new LUNs to this database, is there a way to redistribute the existing data so it is balanced across all files in order to gain the most benefit from having multiple files, rather than just dispersing the additional fragments across the new files?
Will the optimise feature of the maintenance plan do this automatically during the index rebuilds?
Is it better to add more files to the PRIMARY filegroup, or add a number of filegroups with single files in each? We aren't looking to use filegroups for fiddling with our backups by the way.
Many thanks for any recommendations offered.I would opt for more spindles and heads to move the data quicker. Best way I have found is to create new filegroups, then drop primary index on old filegroup and then recreate primary index on desired filegroup. This accomplishes two things.

First, it forces the move of the table data to the new filegroup since the leaf node of primary index **IS** the data page. Secondly, you get not only an index reorg, you get a contiguous page allocation based on the primary index.

Prior to this move, you might want to look at your fill factors to see if they need to be adjusted, because this would be a great time to do that too!|||Also, moving nonclustered indexes in the way described by tomh53 is a great idea as well - not only do you equally distribute your data, but also separate table and its indexes onto different devices, which is generally a good thing to do.sql