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.

Balance Forwarding

Hi,

Just want to seek advice on how to forward the ending balance of Balance Sheet Accounts of previous year to the current year.

Marvs

You can use a couple of MDX function depending on the level you are standed.

If we have the following time heriarchie:

[DimTime].[Year-Month-Day-H] = [Year], [Month], [Day]

And assuming that the [Balance] measure is additive

1) if you drill down to the day level you can obtain the ending balance of the previous year thorugh the following MDX sentence:

(ParallelPeriod([Year],1,[Dim Time].Currentmember).Parent.Lastsibling.Lastchild, measure.[Balance])

2) If you drill down to the month level you can obtain the ending balance of the previous year like this:

(ParallelPeriod([Year],1,[Dim Time].Currentmember).Lastsibling.Lastchild, measure.[Balance])

3) If you are on the year level you can obtain the ending balance of the previous year like this:

([Dim Time].currentmember.Lag(1).Lastchild.Lastchild, measure.[Balance])

And finally you can put the three sentences in two nested iif function, like this:

Iif([DimTime].currentmember.Level.Name = "Day", <Sentence 1>,

Iif([DimTime].currentmember.Level.Name = "Month", <Sentence 2>,

<Sentence 3>))

Other ways using custom rollup formulas can be used.

Hope that helps you

Leandro

|||

What I think you are looking for is the new semi-additive measures in AS2K5, specifically the "last non-empty child" aggregation function. It is designed for balances, quantities on-hand, and a whole class of semi-additive situatons. While this is technically possible on AS2K, it involves a considerable amount of calculations and MDX to evaluate it at runtime. In AS2K5, it is a native aggregation function and performs at the same speed as sum, count, etc.

_-_-_ Dave

|||

That' right.

So the following more simple sentence could be used:

(parallelperiod([Year],1,[DimTime].currentmember), measures.[Balance])

Thanks

Backups from SQL 7 Running under Server 2003

cI had been running SQL 7 under Server 2003 for 5 weeks. I didn't know that 7
should not be run under 2003.
I trying to correct this. ( my current is now inoperable because of HD
problems)
I have backups from this configuration. I'm try to do a recovery using
Server 2000 and SQL 7. It keeps tell me that the backups are invalid.
Have you had experience working with backups created in 7 and 2003 scenario?
You should post the exact command you are running, the location and name of
the backup files, how the backups were made, and the exact error you are
getting...
But the best thing to do is to open a support call with MS if the data is
important... and go ahead and get some progress going now.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"LorenN" <LorenN@.discussions.microsoft.com> wrote in message
news:F1DCBC7E-3541-4DFD-B3E1-C2353141B538@.microsoft.com...
> cI had been running SQL 7 under Server 2003 for 5 weeks. I didn't know
> that 7
> should not be run under 2003.
> I trying to correct this. ( my current is now inoperable because of HD
> problems)
> I have backups from this configuration. I'm try to do a recovery using
> Server 2000 and SQL 7. It keeps tell me that the backups are invalid.
> Have you had experience working with backups created in 7 and 2003
> scenario?

Backups from SQL 7 Running under Server 2003

cI had been running SQL 7 under Server 2003 for 5 weeks. I didn't know that
7
should not be run under 2003.
I trying to correct this. ( my current is now inoperable because of HD
problems)
I have backups from this configuration. I'm try to do a recovery using
Server 2000 and SQL 7. It keeps tell me that the backups are invalid.
Have you had experience working with backups created in 7 and 2003 scenario
?You should post the exact command you are running, the location and name of
the backup files, how the backups were made, and the exact error you are
getting...
But the best thing to do is to open a support call with MS if the data is
important... and go ahead and get some progress going now.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"LorenN" <LorenN@.discussions.microsoft.com> wrote in message
news:F1DCBC7E-3541-4DFD-B3E1-C2353141B538@.microsoft.com...
> cI had been running SQL 7 under Server 2003 for 5 weeks. I didn't know
> that 7
> should not be run under 2003.
> I trying to correct this. ( my current is now inoperable because of HD
> problems)
> I have backups from this configuration. I'm try to do a recovery using
> Server 2000 and SQL 7. It keeps tell me that the backups are invalid.
> Have you had experience working with backups created in 7 and 2003
> scenario?

Backups

Our current SAN capacity has just hit the wall and I need some advice. The
SAN stores our current production SQL db and the backups on another volume.
The volume that the backups for TL's and the database is on is completely
full. Can backups be sent to any server with capacity on the network or
would it be too much overhead? All the the servers are on a fibre switch
between all of the servers so I am not sure it would it would have that much
impact. Moving the backups would give us breathing room for a while until
the SAN is upgraded.Yeah you can - whether it's tolerable, doable or not is
going to depend on the size of the backups and the activity
on the network resources.
-Sue
On Thu, 10 Aug 2006 16:09:00 -0500, "Rockn"
<Rockn@.newsgroups.nospam> wrote:

>Our current SAN capacity has just hit the wall and I need some advice. The
>SAN stores our current production SQL db and the backups on another volume.
>The volume that the backups for TL's and the database is on is completely
>full. Can backups be sent to any server with capacity on the network or
>would it be too much overhead? All the the servers are on a fibre switch
>between all of the servers so I am not sure it would it would have that muc
h
>impact. Moving the backups would give us breathing room for a while until
>the SAN is upgraded.
>|||I would probably do it at it's scheduled time which is currently 12 am when
no one is on the network.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:lgpnd29qne8ajposrnuvtuspbu4gp05t0e@.
4ax.com...
> Yeah you can - whether it's tolerable, doable or not is
> going to depend on the size of the backups and the activity
> on the network resources.
> -Sue
> On Thu, 10 Aug 2006 16:09:00 -0500, "Rockn"
> <Rockn@.newsgroups.nospam> wrote:
>
The[vbcol=seagreen]
volume.[vbcol=seagreen]
much[vbcol=seagreen]
>

Backups

Our current SAN capacity has just hit the wall and I need some advice. The
SAN stores our current production SQL db and the backups on another volume.
The volume that the backups for TL's and the database is on is completely
full. Can backups be sent to any server with capacity on the network or
would it be too much overhead? All the the servers are on a fibre switch
between all of the servers so I am not sure it would it would have that much
impact. Moving the backups would give us breathing room for a while until
the SAN is upgraded.Yeah you can - whether it's tolerable, doable or not is
going to depend on the size of the backups and the activity
on the network resources.
-Sue
On Thu, 10 Aug 2006 16:09:00 -0500, "Rockn"
<Rockn@.newsgroups.nospam> wrote:
>Our current SAN capacity has just hit the wall and I need some advice. The
>SAN stores our current production SQL db and the backups on another volume.
>The volume that the backups for TL's and the database is on is completely
>full. Can backups be sent to any server with capacity on the network or
>would it be too much overhead? All the the servers are on a fibre switch
>between all of the servers so I am not sure it would it would have that much
>impact. Moving the backups would give us breathing room for a while until
>the SAN is upgraded.
>|||I would probably do it at it's scheduled time which is currently 12 am when
no one is on the network.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:lgpnd29qne8ajposrnuvtuspbu4gp05t0e@.4ax.com...
> Yeah you can - whether it's tolerable, doable or not is
> going to depend on the size of the backups and the activity
> on the network resources.
> -Sue
> On Thu, 10 Aug 2006 16:09:00 -0500, "Rockn"
> <Rockn@.newsgroups.nospam> wrote:
> >Our current SAN capacity has just hit the wall and I need some advice.
The
> >SAN stores our current production SQL db and the backups on another
volume.
> >The volume that the backups for TL's and the database is on is completely
> >full. Can backups be sent to any server with capacity on the network or
> >would it be too much overhead? All the the servers are on a fibre switch
> >between all of the servers so I am not sure it would it would have that
much
> >impact. Moving the backups would give us breathing room for a while until
> >the SAN is upgraded.
> >
>

Backup without Data

If I wanted to create another database with the exact same structure of our
current database, MINUS the data...is there an easy/quick way to do this?
If there's an automated way to "reset" the Identity fields as well, that
would be nice, but I could do that relatively quickly.just script the entire database out of Enterprise Manager.
(Right click on the DB and choose "all Tasks"..."Generate Script"
there are a few options you'll need to choose in the dialog (script all
objects, script database, script object level permissions, etc). after a few
tries, you'll be an old pro.
Greg Jackson
PDX, Oregon|||> (Right click on the DB and choose "all Tasks"..."Generate Script"
> there are a few options you'll need to choose in the dialog (script all
> objects, script database, script object level permissions, etc). after a
few
> tries, you'll be an old pro.
Thanks for the help. I ran through this process and "scripted" everything.
When I looked at the *.sql file it generates, it looks like it would drop my
current database and completely erase it essentially. This isn't what I'm
looking for. I want to create a new database with the exact same structure,
but the last thing I want to do is drop our current one.|||Use DTS...
Choose the "Copy objects and data between SQL Server databases"
Here is the key... on the next screen, uncheck "Copy Data"
"James" <letyoudown@.verizon.net> wrote in message
news:ej$kp2pdFHA.3880@.tk2msftngp13.phx.gbl...
> > (Right click on the DB and choose "all Tasks"..."Generate Script"
> >
> > there are a few options you'll need to choose in the dialog (script all
> > objects, script database, script object level permissions, etc). after a
> few
> > tries, you'll be an old pro.
> Thanks for the help. I ran through this process and "scripted"
everything.
> When I looked at the *.sql file it generates, it looks like it would drop
my
> current database and completely erase it essentially. This isn't what I'm
> looking for. I want to create a new database with the exact same
structure,
> but the last thing I want to do is drop our current one.
>|||Just uncheck the "drop existing" box in the script generation dialog, then
edit the db create line in the script, so it contains the new name.
"James" wrote:
> > (Right click on the DB and choose "all Tasks"..."Generate Script"
> >
> > there are a few options you'll need to choose in the dialog (script all
> > objects, script database, script object level permissions, etc). after a
> few
> > tries, you'll be an old pro.
> Thanks for the help. I ran through this process and "scripted" everything.
> When I looked at the *.sql file it generates, it looks like it would drop my
> current database and completely erase it essentially. This isn't what I'm
> looking for. I want to create a new database with the exact same structure,
> but the last thing I want to do is drop our current one.
>
>|||Thank you all...worked just fine. I just get nervous about doing anything
where one checked/unchecked box could cost me a ton of time restoring a
backup.|||yes you need to be careful....
you didnt see the fine print in my first post ?
:-)
Greg Jackson
PDX, Oregon