Best SQL backup server design - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Best SQL backup server design

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-08-04 : 14:33:39

My previous method of having a backup SQL server was A PDC (nt4.0- SQL2000) named server1 and a BDC (nt4.0-SQL2000) named server2 I ran replicaton to server2 to keep a constant copy of my critical dbs.

All I had to do was rename the BDC after it was Promoted to a PDC to Server1 and we were back up an running.

Now we are on 2003 server with active directory and DNS running on both boxes.

Renameing a DNS sever is a complicated process.

What would be the best method of having a redundent SQL server in this enviroment.

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-04 : 15:34:09

Nobody responded yet?

Sorry can't help....we don't do it that way....

And I got some Prod dbas that manage the bit head stuff...

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-08-04 : 15:41:48

We were forced into the 2003 server upgrade on short notice to support the new phone system so I was not able to develop a plan for a backup SQL server.

Right now I have no backup SQL server so I am open to suggestions.

I was taking a look at clustering but am not familiar with it so I do not know the pros and cons and it looks like it needs 2003 enterprise version and of course we bought standard.

How do you do it Brett.

Jim
Users <> Logic

MuadDBA

628 Posts

Posted - 2004-08-04 : 15:50:28

Look up "Log Shipping" in books online or search for it here on the forums, you will come up with a million ways on how to implement it so you have a warm standby server very similar to when you were doing replication. Replication, though, was primarily designed for reporting and synchronizing individual tables, not entire databases, so Log Shipping is a better option.

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-04 : 15:56:44

2 boxes

1 in Jersey

1 in an undisclosed location...but it's not Vegas like Tara gets...Damn

db dumps nightly.

dump is compressed and shipped to the backup

Now I'm not sure, but we either restore the backup or just save it..

Anyway the tranny logs are dumped every 15 minutes and shipped as well..

Those guys are applied as soon as they are recevied (that's why I think we don't need to do the full restore...unless there's a problem)

That's it...

When we did or tests, I know they flipped the ip and everything worked like a champ...

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-08-04 : 16:02:10

quote:
Originally posted by crazyjoe

Look up "Log Shipping" in books online or search for it here on the forums, you will come up with a million ways on how to implement it so you have a warm standby server very similar to when you were doing replication. Replication, though, was primarily designed for reporting and synchronizing individual tables, not entire databases, so Log Shipping is a better option.

My problem Lies in the name of the server all applications point to Server1 I can have a redundent copy on server2 but the application will not go to it.

Jim
Users <> Logic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-08-04 : 16:16:34

I was gonna chime in here but didn't have any sound advice with respect to the renaming of you domain controller (there would be only one left correct; when disaster strikes: that breaks the elegence of getting out of the AD then rejoining).
One more dedicated domain controller, bring it up then remove the domain controller off the SQL box would be your best bet I think but a little money would be needed(hopefully just a little). Depending on the corporate needs I'd log ship to just about anything other than the backup domain controller as well then the rename, is clean with the assurance that we may be slow but not down. And my AD network is intact. Cause like you said, some configurations aren't easy. "Ah what's in a name"

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-08-04 : 16:31:31

1st domain controler is seperate from my SQL server But the backup box is both. Adding another box would start a screeming session.

Jim
Users <> Logic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-08-04 : 16:57:02

Log shipping it is then.
Just put primary DNS on 1st domain controller, that really is a non definition and
only goes so far as client IP stack in most cases. Don't quote that one it's just
the way the "black box" appears

Disaster recovery is
SQL goes down > Manage Your Server(on log shipped backup server) > make it stop being a DNS and AD > then remove from domain > then rejoin Domain with new name

Let it flow, check existence and disappearance of computer accounts in AD and IP's in DNS so all seems automated
after leaving and coming back. Refresh etc. Or manually configure so we are stepping through it. That's how I've done it and I like seeing it work. I think you can "slam" rename 200x but haven't had success that way.

That plan has only one extra juggle from a pure log shipping setup
but once recovered you are without a redundant AD/DNS

Evaluate that result as acceptable or not, really business dependent.
I think it's closer to good, but that is with a lot of assumptions.

It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-08-04 : 17:08:42

I was afraid of that.

According to BOL Log shipping is available only in enterprize version witch of course I do not have.

Looks Like Replication and rename procidure with redundent backups.

$#it.

Jim
Users <> Logic

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-04 : 19:22:38

You can setup your own log shipping Jim. You just do full and transaction log backups. Have a job on the backup server that restores them. It's not that hard to setup. Also, why do you have SQL Server on your PDC and BDC?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-05 : 03:07:30

quote:
Originally posted by derrickleggett

Also, why do you have SQL Server on your PDC and BDC?

Indeed. That's the critical issue, not the renaming of server stuff.

If you need quick/instant failover, spend the cash and set up clustering.

Either way, get the SQL seperated from your domain controllers. The PDC (or domain master, whatever they call it now) (at the very least) should be doing as little as possible. (IE not running your core apps).

-------
Moo. :)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-08-05 : 07:31:58

My Primary SQL server is just a SQL Server nothing else.

My backup is in another building connected by Fiber.

Space and funds forced me to make it both SQL and DC/AD (was BDC).

Other than The cost What are the drawbacks to Clustering?

Note: I run my remote offices via Citrix through the Internet.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-05 : 14:21:27

Replication is not a disastery recovery solution. Write your own log shipping stored procedures.

The application should be pointing to an alias rather than a specific database server. Then when you move to the standby server, the alias would not point to the other database server. The alias could be a DNS alias or it could be SQL Client Network Utility thing. Either way, there would be some intervention needed here. Or use clustering and then you'd refer to the virtual names and wouldn't care about aliases.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources