How to Replicate an SQL Server 2000 Database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bondwireless - 21 Jun 2006 13:12 GMT

Hi, i am new to this so really need some helpfull and easy to follow
information so that i can successfully replicate my SQL Server Database.

Here is my dilema. I have a working SQL Server 2000 Database which has an IP
address and alias so can be connected to by the outside world. However on the
machine it is running on, it is called (local).

Now when i went to use the replication wizard it won't let me with my
database being called (local) and i can't seem to rename it without
re-registering it. Would this entail creating a new Database and copying all
the tables across? How would this effect my users connecting to my Database
(since there will be an introduction of a name for my database as apposed to
the one called (local))

Once this is all done, what are my options? Can i simply replicate my entire
database to another hard drive? Can i replicate it to another server running
SQL Server 2000 with no database configured? Or would i have to have a copy
of the DB already on the other server?

Once replication is done is there a way of keeping the databases
synchronised so that in the case of a failure on the first Server, i could
switch over to the backup Server? Would synchronisation be automatic or would
i have to do it manually?

Your answers would be greatly appreciated.

Signature

Regards
Glen Pankhurst
Research & Development Manager

Reply to this Message

Hilary Cotter - 21 Jun 2006 13:20 GMT

Go to a command prompt and type hostname. This is your computer name

Then in QA do the following

EXEC sp_dropserver '(local)
GO
EXEC sp_addserver 'Name Returned from hostname', 'local'
GO

And then stop and start SQL Server. Try again.

Your users will be able to connect as before only they will be disconnected
as you restart.

Note further that you should be using transactional replication (assuming
all of your tables have primary keys).

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hi, i am new to this so really need some helpfull and easy to follow
> information so that i can successfully replicate my SQL Server Database.
[quoted text clipped - 30 lines]
>
> Your answers would be greatly appreciated.

Reply to this Message

Bondwireless - 21 Jun 2006 13:32 GMT

Hi Hilary,

I am assuming QA means Query Analyser?

If i do this in Query Analyser should i have the DB closed? As well as the
Apache Tomcat Windows which accept input from Clients and relay from DB?

Are you 100% sure this will not effect any of my coding or clients?
And do i just restart SQL or the Server?

Now regarding Replication, i am brand new to this... is there a way of just
replicating the database (mdl) files to another Hard Drive somewhere? Or do i
need to set up another machine with SQL Server and start replicating to this?

Thanks for your quick response.

Signature

Regards
Glen Pankhurst
Research & Development Manager

> Go to a command prompt and type hostname. This is your computer name
>
[quoted text clipped - 47 lines]
> >
> > Your answers would be greatly appreciated.

Reply to this Message

Hilary Cotter - 21 Jun 2006 15:08 GMT

Hi Blondwireless.

I do mean Query Analyzer.

Your clients will be affected as you restart SQL Server;) but other than
that there will be some locking as the snapshot is generated and some
performance degradation (although slight 5-10% tops) as you are replicating.

For replication you will need a second machine with SQL Server running on
it. You could replicated to another database on the same server if you wish.

Exactly what are you trying to accomplish and why? This will help us with
further advice.

Signature

> Hi Hilary,
>
[quoted text clipped - 73 lines]
>> >
>> > Your answers would be greatly appreciated.

Reply to this Message

Bondwireless - 22 Jun 2006 01:54 GMT

Hi Hilary,

thank you for your advice.

Basically what i am trying to do is have a redundant and possibly faster
performing database that is scalable.

Replication as i understand it allows only 1 server to be active while the
other is replicated to.

I am weighing up Clustering vs Replication but i am not 100% sure what the
implications are. In either respect i gather we need a second server running
pretty much identical hardware, OS and software.
And with replication the hard drive inside the second server would be used
for the backup.
What about with clustering? I heard of a hard drive array. Could the hard
drives in the respective machines be configured to act as an array or do i
actually need to get some sort of external storage device that both can
access so that they can cluster to? If this is the case is any of the
database stored on the local servers or just on the cluster hard drive
sitting externally to both.

Will having a cluster array in an active active situation actually boost
performance as both servers respond to queries?
We are also currently using SQL Server 2000 and i heard clustering works but
is very slow with it. So, when i decide on a plan of attack i want to upgrade
to SQL 2005 anyway. (will this fix the the slow speed of clustering
apparently experience in SQL 2000)

Signature

Regards
Glen Pankhurst
Research & Development Manager

> Hi Blondwireless.
>
[quoted text clipped - 87 lines]
> >> >
> >> > Your answers would be greatly appreciated.

Reply to this Message

Bondwireless - 22 Jun 2006 15:10 GMT

Hi again Hilary,

i had two problems trying your suggestion of renaming the SQL Database from
local to the computer name.

i logged into sql query analyser first as windows authentication and this is
what is got:

"Server: Msg 15015, Level 16, State 1, Procedure sp_dropserver, Line 28
The server '(local)' does not exist. Use sp_helpserver to show available
servers.
Server: Msg 15028, Level 16, State 1, Procedure sp_addserver, Line 14
The server 'COMPUTERNAME' already exists."

Then i logged in as i do to administer the Database remotely and got this:

"Server: Msg 15247, Level 16, State 1, Procedure sp_dropserver, Line 18
User does not have permission to perform this action.
Server: Msg 15028, Level 16, State 1, Procedure sp_addserver, Line 14
The server 'COMPUTERNAME' already exists."

Signature

Regards
Glen Pankhurst
Research & Development Manager

> Hi Blondwireless.
>
[quoted text clipped - 87 lines]
> >> >
> >> > Your answers would be greatly appreciated.

Reply to this Message

Bondwireless - 22 Jun 2006 15:27 GMT

Okay i tried what one of the error messages said to do, use "sp_helpserver"
which returned the name of the computer that i am trying to change local to.
So is it already called that?

But when i load Enterprise Manager and connect to the database the DB Name
under Edit SQL Server Registration Properties, Registered SQL Server
Properties, the server name is greyed out and says local.

When i try to replicate i get this message "SQL Server replication does not
support nicknames, such as "." or "(local)", as server names. Delete the SQL
Server registration for this server and register it using the actual server
name."

Do i really have to take the database plus applications offline. Register a
new database with the correct computer name and import the database files?
What will the implications be?
Will usernames and passwords be intact?
What would i have to recreate?
Is there anyway around this avenue?

Signature

Regards
Glen Pankhurst
Research & Development Manager

> Hi again Hilary,
>
[quoted text clipped - 108 lines]
> > >> >
> > >> > Your answers would be greatly appreciated.

Reply to this Message

MADNESS - 26 Jun 2006 21:00 GMT

Hi Bondwireless,

Delete the local ENT Manager registration on the server and add a new
registration using the SERVERNAME. You should then be able to setup
replication using this registration. Alternatively connect to the server from
your pc using the COMPUTERNAME as the registration and that should work also.

> Okay i tried what one of the error messages said to do, use "sp_helpserver"
> which returned the name of the computer that i am trying to change local to.
[quoted text clipped - 128 lines]
> > > >> >
> > > >> > Your answers would be greatly appreciated.

Reply to this Message

mcoste66 - 25 Sep 2007 18:08 GMT

I would like to butt in if I may...that still doesn't answer the main
question; will deleting the 'local' registration and creating a new
registration using the actual server name delete or otherwise mess with my
existing data in any way? (I would be using wizards to do this, not command
lines)

> Hi Bondwireless,
>
[quoted text clipped - 135 lines]
> > > > >> >
> > > > >> > Your answers would be greatly appreciated.

Reply to this Message

Hilary Cotter - 27 Sep 2007 15:12 GMT

No, it won't you will have to recreate it yourself.

Signature

RelevantNoise.com - dedicated to mining blogs for business intelligence.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

>I would like to butt in if I may...that still doesn't answer the main
> question; will deleting the 'local' registration and creating a new
[quoted text clipped - 186 lines]
>> > > > >> >
>> > > > >> > Your answers would be greatly appreciated.

Reply to this Message

mcoste66 - 27 Sep 2007 17:39 GMT

What about any interfaces or connections of any kind that access the 'local'
database? If I delete that registration, then create a new one (using the
actual server name) enterprise manager should associate the database with the
new registration, correct? What about any and all connections like ODBC, etc?

> No, it won't you will have to recreate it yourself.
>
[quoted text clipped - 188 lines]
> >> > > > >> >
> >> > > > >> > Your answers would be greatly appreciated.

Reply to this Message

Hilary Cotter - 28 Sep 2007 13:02 GMT

They will be fine. Think of . or local as an alias of the NetBIOS name.
Replication needs the NetBIOS name for it to work, other application will
still be able to use the . and local aliases.

Signature

> What about any interfaces or connections of any kind that access the
> 'local'
[quoted text clipped - 225 lines]
>> >> > > > >> >
>> >> > > > >> > Your answers would be greatly appreciated.

Reply to this Message

mcoste66 - 28 Sep 2007 21:50 GMT

Cool, thx...I'll try that.

> They will be fine. Think of . or local as an alias of the NetBIOS name.
> Replication needs the NetBIOS name for it to work, other application will
[quoted text clipped - 204 lines]
> >> >> > > > >> > to
> >> >> > > > >> > the one called (local))

Reply to this Message