Re: How to Replicate an SQL Server 2000 Database

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

--
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
"mcoste66" <mcoste66@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:72A15FDA-1CE6-4A80-B2D8-178899C2E3D4@xxxxxxxxxxxxxxxx
I would like to *** 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)

"MADNESS" wrote:

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.

"Bondwireless" wrote:

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?
--
Regards
Glen Pankhurst
Research & Development Manager


"Bondwireless" wrote:

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."
--
Regards
Glen Pankhurst
Research & Development Manager


"Hilary Cotter" wrote:

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.

--
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



"Bondwireless" <Bondwireless@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:8B3747E4-2D38-40E0-A072-EDBDC3431556@xxxxxxxxxxxxxxxx
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.

--
Regards
Glen Pankhurst
Research & Development Manager


"Hilary Cotter" wrote:

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).

--
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



"Bondwireless" <Bondwireless@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:FBE1D332-25DC-405D-9F8A-7B651B11F96C@xxxxxxxxxxxxxxxx
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.

--
Regards
Glen Pankhurst
Research & Development Manager








.

Loading