reduce databse size: redundancy

Showing posts with label redundancy. Show all posts

Wednesday, March 7, 2012

Redundancy requiring Identity columns on Subscriber - please help

Hi
I am just getting up to speed on replication so please forgive me if I
am missing something simple here......
Scenario
Application server (call it Test) that has SQL Server 2000 database
I am setting up replication for backup pupopses
(If Server A dies - point Application (Test) to Server B )
My Problem -
Many tables in the database have the identiy column set to YES
As the TEST operates, many rows uniquely added to the db using Identity
column as PKeys
I was using snapshot replication for test purposes....(no license for
Transactional)
On subscriber - created blank DB with YES (Not for Replication) option
set for the tables in question.
The Snapshot I created from Publisher, when I push to the subscriber -
it resets the Identify (YES - NFR) back to No
I redireecte dteh Aopplication server to Server B an dit fails to work
corrcectly.- cannot insert into row s where Identity column is set to
No
Identify needs to be set to YES (or YES not for Repl) in order for my
application server to work correctly.
Any ideas on how I could best achieve this?
Thanks in advance........
Kevha
right click on your publication, select properties, in the articles tab
select the browse button to the right of your tables. Click on the snapshot
tab. In the name conflicts section, select the keep delete existing data.
Now return to your subscriber, fix things the way you want them, and then
rereun your snapshot.
It should work this time.
Hilary Cotter
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
<kevha@.hotmail.com> wrote in message
news:1132178886.227006.217160@.o13g2000cwo.googlegr oups.com...
> Hi
> I am just getting up to speed on replication so please forgive me if I
> am missing something simple here......
> Scenario
> Application server (call it Test) that has SQL Server 2000 database
> I am setting up replication for backup pupopses
> (If Server A dies - point Application (Test) to Server B )
> My Problem -
> Many tables in the database have the identiy column set to YES
> As the TEST operates, many rows uniquely added to the db using Identity
> column as PKeys
> I was using snapshot replication for test purposes....(no license for
> Transactional)
> On subscriber - created blank DB with YES (Not for Replication) option
> set for the tables in question.
> The Snapshot I created from Publisher, when I push to the subscriber -
> it resets the Identify (YES - NFR) back to No
> I redireecte dteh Aopplication server to Server B an dit fails to work
> corrcectly.- cannot insert into row s where Identity column is set to
> No
> Identify needs to be set to YES (or YES not for Repl) in order for my
> application server to work correctly.
> Any ideas on how I could best achieve this?
> Thanks in advance........
> Kevha
>
|||Hi Hilary
I still can't seem to get the problem solved.
Let me take it back a step -
Server A (source)
Server B (subscriber)
I want to replicate server A to server B
I set up Server A with Snapshot Replicationa sPublisher and
Distributor.
Database created on Server A - some tables with Columns set with
Identity = Yes
I set up Snapshot Replication
In the SnapShot Properties/ Articles I set 'Keep Existing Table
unchanged' option on.
I added a custom script in the Snapshot Properties tab that - Drops the
tables, recreates them with the IDENTITY NOT FOR REPLICATION option
included during table creation.
When I conduct the Synchronize - I get the following error -
Violation of Primary Key constraint 'x' insert duplicate key in object
'x'
My replication could happen once an hour (not real time critical so any
option that keeps the Identity filed intact appreciated)

Redundancy Question

Dear All
We have a server running Windows 2000 Server with SQL 2000. This is our web
server, and what I would like to do is implement some form of redundancy
server within this environment. Ideally, what I would like to achieve is a
scenario where is if there is a failure on one of the servers another one
would start up ad take on the exact same role with the exact same access and
sql database and applications.
Does anyone have any idea how this would be achieved, and point in the right
direction.
Thanks
Neil
Hi,
You can go for below options:-
1. Transactional Replication
2. Logshipping every 5 minutes.
In the above cases you have to change your IP Address of new server with old
server when there is a requirement
Thanks
Hari
MCDBA
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>
|||Have a look at
http://www.microsoft.com/sql/techinf...ailability.asp
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>

Redundancy Question

Dear All
We have a server running Windows 2000 Server with SQL 2000. This is our web
server, and what I would like to do is implement some form of redundancy
server within this environment. Ideally, what I would like to achieve is a
scenario where is if there is a failure on one of the servers another one
would start up ad take on the exact same role with the exact same access and
sql database and applications.
Does anyone have any idea how this would be achieved, and point in the right
direction.
Thanks
NeilHi,
You can go for below options:-
1. Transactional Replication
2. Logshipping every 5 minutes.
In the above cases you have to change your IP Address of new server with old
server when there is a requirement
Thanks
Hari
MCDBA
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>|||Have a look at
http://www.microsoft.com/sql/techin...vailability.asp
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>

Redundancy Question

Dear All
We have a server running Windows 2000 Server with SQL 2000. This is our web
server, and what I would like to do is implement some form of redundancy
server within this environment. Ideally, what I would like to achieve is a
scenario where is if there is a failure on one of the servers another one
would start up ad take on the exact same role with the exact same access and
sql database and applications.
Does anyone have any idea how this would be achieved, and point in the right
direction.
Thanks
NeilHave you looked at Replication yet ?
With this you can automatically copy information for one
database to another automatically.
Look up Snapshot and Transactional Replication.
J
>--Original Message--
>Dear All
>We have a server running Windows 2000 Server with SQL
2000. This is our web
>server, and what I would like to do is implement some
form of redundancy
>server within this environment. Ideally, what I would
like to achieve is a
>scenario where is if there is a failure on one of the
servers another one
>would start up ad take on the exact same role with the
exact same access and
>sql database and applications.
>Does anyone have any idea how this would be achieved, and
point in the right
>direction.
>Thanks
>Neil
>
>.
>|||Hi,
You can go for below options:-
1. Transactional Replication
2. Logshipping every 5 minutes.
In the above cases you have to change your IP Address of new server with old
server when there is a requirement
Thanks
Hari
MCDBA
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>|||Have a look at
http://www.microsoft.com/sql/techinfo/administration/2000/availability.asp
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Neil Shirley" <neils@.nospam.complete-it.co.uk> wrote in message
news:epDILRYPEHA.1612@.TK2MSFTNGP11.phx.gbl...
> Dear All
> We have a server running Windows 2000 Server with SQL 2000. This is our
web
> server, and what I would like to do is implement some form of redundancy
> server within this environment. Ideally, what I would like to achieve is
a
> scenario where is if there is a failure on one of the servers another one
> would start up ad take on the exact same role with the exact same access
and
> sql database and applications.
> Does anyone have any idea how this would be achieved, and point in the
right
> direction.
> Thanks
> Neil
>

Redundancy

Hi All,

After some feedback from some of you who might have done something similar:

We are going to be having 3 SQL servers (running SQL Std licence).
2 Are live
1 is a hot swap in the event that we have a total loss of either of the SQL boxes.
Basically what I am wanting to do, is have the hot swap being updated periodically so that the databases are being replicated on this box, so that if the live one fell over, we could very quickly get the hot swap into take over.

Can anyone offer any perspectives on the best method of attack for this?

Thanks in advance for your thoughts.

CheersTry to implement your own log shipping by referring to this article http://www.sql-server-performance.com/sql_server_log_shipping.asp and this http://support.microsoft.com/default.aspx?scid=kb;en-us;314515 KBA for FAQs about LS.|||Brilliant - thanks this is exactly what I was after.

Thanks!|||This is only a thought, but you could also do transactional replication to the third box. If one of the two other servers dies, all you need to do is drop all of the replication and rename the third box to take the place of the downed server.

This buys two advantages from my perspective... First, it gives you a "live" set of data that you can query, etc. to take load off of your production database(s). As a nice side benefit, it gives you literally "up to the minute" data, there is no loss due to log shipping delays if you ever need to use it!

-PatP

Redundancy

Is there any untility, program, or script that I can use to backup a SQL
database and will look for the main SQL server? If for anyreason that server
is not seen on the network a backup server will pick up from the last back up
and carry on where the first server left off. It seems logical there would
be.For starters, check out Fail-over Clustering in the SQL Server Books Online.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
> Is there any untility, program, or script that I can use to backup a SQL
> database and will look for the main SQL server? If for anyreason that
> server
> is not seen on the network a backup server will pick up from the last back
> up
> and carry on where the first server left off. It seems logical there
> would
> be.|||Hi
For the various highg availablility options check out:
http://www.microsoft.com/sql/techinfo/administration/2000/availability.asp
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
> Is there any untility, program, or script that I can use to backup a SQL
> database and will look for the main SQL server? If for anyreason that
> server
> is not seen on the network a backup server will pick up from the last back
> up
> and carry on where the first server left off. It seems logical there
> would
> be.|||Dan,
Would this only work on either NT or 2k Server or could this be done on any
MS OS? Exactly what I am trying to solve is this: I am working with a Point
of Sale software that uses either MSDE or SQL Server for the larger sites to
control the databases. My problem is what happens if the main server goes
down? Clustering could be an option. Any other ideas?
"Dan Guzman" wrote:
> For starters, check out Fail-over Clustering in the SQL Server Books Online.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
> > Is there any untility, program, or script that I can use to backup a SQL
> > database and will look for the main SQL server? If for anyreason that
> > server
> > is not seen on the network a backup server will pick up from the last back
> > up
> > and carry on where the first server left off. It seems logical there
> > would
> > be.
>
>|||> Would this only work on either NT or 2k Server or could this be done on
> any
> MS OS?
You need SQL Server Enterprise edition as well as one of the server OS
editions below. From the Books Online Fail-over Clustering overview:
<Excerpt href-"adminsql.chm::/ad_clustering_7t9v.htm">
Before installing failover clustering, you must install Microsoft Windows
NT® 4.0, Enterprise Edition, Microsoft Windows® 2000 Advanced Server or
Windows 2000 Datacenter Server, and the Microsoft Cluster Service (MSCS).
</Excerpt>
Additionally, you need to have supported hardware, including shared disks.
Clustering may be an option for you larger POS implementations but not for
the MSDE ones.
There are some new high-availability features in the upcoming SQL 2005
version that don't require specialized clustering hardware. See the
high-availability demo at
http://www.microsoft.com/sql/2005/productinfo/demos/hademo.asp
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3D25F3F5-E235-41CA-9693-2E176116AA8A@.microsoft.com...
> Dan,
> Would this only work on either NT or 2k Server or could this be done on
> any
> MS OS? Exactly what I am trying to solve is this: I am working with a
> Point
> of Sale software that uses either MSDE or SQL Server for the larger sites
> to
> control the databases. My problem is what happens if the main server goes
> down? Clustering could be an option. Any other ideas?
> "Dan Guzman" wrote:
>> For starters, check out Fail-over Clustering in the SQL Server Books
>> Online.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
>> > Is there any untility, program, or script that I can use to backup a
>> > SQL
>> > database and will look for the main SQL server? If for anyreason that
>> > server
>> > is not seen on the network a backup server will pick up from the last
>> > back
>> > up
>> > and carry on where the first server left off. It seems logical there
>> > would
>> > be.
>>|||Paul,
SQL Server Enterprise Edition is going to cost you a fair bit. One of the
best third party products I have seen is XOSoft's WANYSyncHA.
see: www.xosoft.com
The WANYSyncHA product allows automatic failover to another server, with
continously replicating changes at the byte level.
It also includes Data Rewind which allows you to restore the database to a
particular snapshot should both databases become corrupt.
Regards,
Andrew
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3D25F3F5-E235-41CA-9693-2E176116AA8A@.microsoft.com...
> Dan,
> Would this only work on either NT or 2k Server or could this be done on
any
> MS OS? Exactly what I am trying to solve is this: I am working with a
Point
> of Sale software that uses either MSDE or SQL Server for the larger sites
to
> control the databases. My problem is what happens if the main server goes
> down? Clustering could be an option. Any other ideas?
> "Dan Guzman" wrote:
> > For starters, check out Fail-over Clustering in the SQL Server Books
Online.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
> > > Is there any untility, program, or script that I can use to backup a
SQL
> > > database and will look for the main SQL server? If for anyreason that
> > > server
> > > is not seen on the network a backup server will pick up from the last
back
> > > up
> > > and carry on where the first server left off. It seems logical there
> > > would
> > > be.
> >
> >
> >|||Correction, product is WANSynchHA, not WANYSyncHA..
Andrew
"Andrew" <me@.anonymous.com> wrote in message
news:41bf7b50$1_1@.news.iprimus.com.au...
> Paul,
> SQL Server Enterprise Edition is going to cost you a fair bit. One of the
> best third party products I have seen is XOSoft's WANYSyncHA.
> see: www.xosoft.com
> The WANYSyncHA product allows automatic failover to another server, with
> continously replicating changes at the byte level.
> It also includes Data Rewind which allows you to restore the database to a
> particular snapshot should both databases become corrupt.
> Regards,
> Andrew
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:3D25F3F5-E235-41CA-9693-2E176116AA8A@.microsoft.com...
> > Dan,
> >
> > Would this only work on either NT or 2k Server or could this be done on
> any
> > MS OS? Exactly what I am trying to solve is this: I am working with a
> Point
> > of Sale software that uses either MSDE or SQL Server for the larger
sites
> to
> > control the databases. My problem is what happens if the main server
goes
> > down? Clustering could be an option. Any other ideas?
> >
> > "Dan Guzman" wrote:
> >
> > > For starters, check out Fail-over Clustering in the SQL Server Books
> Online.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:64D81DCB-E75E-437C-9FE4-D007EB19B016@.microsoft.com...
> > > > Is there any untility, program, or script that I can use to backup a
> SQL
> > > > database and will look for the main SQL server? If for anyreason
that
> > > > server
> > > > is not seen on the network a backup server will pick up from the
last
> back
> > > > up
> > > > and carry on where the first server left off. It seems logical
there
> > > > would
> > > > be.
> > >
> > >
> > >
>

Wednesday, March 7, 2012

Redundancy requiring Identity columns on Subscriber - please help

Redundancy Question

Redundancy Question

Redundancy Question

Redundancy

Redundancy

Redundancy

reduce databse size

Blog Archive

About Me