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.

Backward Coompatibility

We have two different machines in different locations running SQL2000. One was upgraded to SQL2005. The problem is we can't upgrade the other at the moment cause it is running SBS2003 (requires alot more planning), But we would like to get information from the computer running SQL2005. Is there a patch that can be obtained so that we can open the database created using SQL2005 in SQL2000? We desperately need the database and upgrading to SQL2005 with SBS2003 is not straight forward.
Database files or backups created in SQL Server 2005 cannot be opened using SQL Server 2000. YOu sure can connect to the SQL Server 2005 server and get data but you are not able to use the physical files for restore or attaching.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||If you have the Sql2000 backup in tape \ disk before upgrading. Use the backup and restore the db in Sql2000, Use Import\Export to move all the data's from Sql2005 to Sql2000 for that db. Since you don't have any option for using Sql2005 physical or backup files in sql2000.

Backup to a share

Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
a session on both machines is "Administrator", "password". SQL server account
is the same (administrator).
I on the sql box, I create a share to the 2nd box. I can create, deleteor do
anything on this share. However, when I create a maintenance plan to backup
some DBs to this share, the job fails.
I tried UNC,it did not work neither.
How can or is it possible to backup to a share?
ThanksOf course this is possible. Start by giving permissions on that share to the
Windows account used by the SQL Server service, that is, that account must be
able to write files to that share. Connect as that account and make sure you
have access to the share.
If the job still does not work go to the View History section of the job and
look for error messages. Also take a look at the SQL Server error log.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"SalamElias" wrote:
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
> a session on both machines is "Administrator", "password". SQL server account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor do
> anything on this share. However, when I create a maintenance plan to backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks|||SQL Server itself (and SQL Server Agent) are not running as Administrator,
are they? When you map a drive letter, it is mapped for YOUR username only.
This drive letter is not visible to other users (e.g. the user that SQL
services run as).
When you tried a UNC path, what does "did not work" mean? Do you get an
error message? If so, what is it?
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
For SQL 2000, but it works just the same for SQL 2005 as far as required
permissions.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks|||As I said, both credetnials are the same and I can create, delete modify any
file in the share which are used to run both services SQL server and agnet
When editing the MP, in the backup task page, I click on the button with 3
dots opposite to folder, I see only the local disks C:\ and E:\ not the
mapped share Z:\backup.
Regards
"Ben Nevarez" wrote:
> Of course this is possible. Start by giving permissions on that share to the
> Windows account used by the SQL Server service, that is, that account must be
> able to write files to that share. Connect as that account and make sure you
> have access to the share.
> If the job still does not work go to the View History section of the job and
> look for error messages. Also take a look at the SQL Server error log.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "SalamElias" wrote:
> > Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
> > a session on both machines is "Administrator", "password". SQL server account
> > is the same (administrator).
> > I on the sql box, I create a share to the 2nd box. I can create, deleteor do
> > anything on this share. However, when I create a maintenance plan to backup
> > some DBs to this share, the job fails.
> > I tried UNC,it did not work neither.
> > How can or is it possible to backup to a share?
> >
> > Thanks

Backup to a share

Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
a session on both machines is "Administrator", "password". SQL server account
is the same (administrator).
I on the sql box, I create a share to the 2nd box. I can create, deleteor do
anything on this share. However, when I create a maintenance plan to backup
some DBs to this share, the job fails.
I tried UNC,it did not work neither.
How can or is it possible to backup to a share?
Thanks
Of course this is possible. Start by giving permissions on that share to the
Windows account used by the SQL Server service, that is, that account must be
able to write files to that share. Connect as that account and make sure you
have access to the share.
If the job still does not work go to the View History section of the job and
look for error messages. Also take a look at the SQL Server error log.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"SalamElias" wrote:

> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
> a session on both machines is "Administrator", "password". SQL server account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor do
> anything on this share. However, when I create a maintenance plan to backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks
|||SQL Server itself (and SQL Server Agent) are not running as Administrator,
are they? When you map a drive letter, it is mapped for YOUR username only.
This drive letter is not visible to other users (e.g. the user that SQL
services run as).
When you tried a UNC path, what does "did not work" mean? Do you get an
error message? If so, what is it?
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
For SQL 2000, but it works just the same for SQL 2005 as far as required
permissions.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks
|||As I said, both credetnials are the same and I can create, delete modify any
file in the share which are used to run both services SQL server and agnet
When editing the MP, in the backup task page, I click on the button with 3
dots opposite to folder, I see only the local disks C:\ and E:\ not the
mapped share Z:\backup.
Regards
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Of course this is possible. Start by giving permissions on that share to the
> Windows account used by the SQL Server service, that is, that account must be
> able to write files to that share. Connect as that account and make sure you
> have access to the share.
> If the job still does not work go to the View History section of the job and
> look for error messages. Also take a look at the SQL Server error log.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "SalamElias" wrote: