Hello;
I have worked my way through numerous connection problems and login
issues, but Im left with one problem Im hoping I can get some help
on:
Ive got an SQL 2000 v 8.00.760 SP3a, with a production database Im
trying to replicate from one workgroup to another (yes, workgroups
ack!) Ive got the connections all worked out, it was a few days of
fun...
Ive setup a transactional replication between a production database
and a blank database.
The database is about 1.5Gb so its a hefty size.
I setup the transactional replication to include an initialization of
the Schema and Data, and asked the wizard to start the snapshot agent
and begin the initialization process immediately.
The Subscription creates and the snapshot is successful.
However, the Push fails with the following error: "Unable to
replicate a view or function because the referenced objects or columns
are not present on the subscriber"
Any help would be greatly appreciated!
1st time poster, Im impressed with the knowledge of the boards
members, but Im new, so go easy on me if its obvious to you :D
EDIT: After looking through the replication, I have noticed that there
are 3x as many tables in the Production database as in the replicated
database, is this the snapshot not pulling properly, or implementing
properly?
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict233874.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=810666
"SNA2" wrote:
> Hello;
> I have worked my way through numerous connection problems and
> login issues, but I'm left with one problem I'm hoping I can
> get some help on:
> I've got an SQL 2000 v 8.00.760 SP3a, with a production
> database I'm trying to replicate from one workgroup to another
> (yes, workgroups ack!) I've got the connections all worked
> out, it was a few days of fun...
> I've setup a transactional replication between a production
> database and a blank database.
> The database is about 1.5Gb so it's a hefty size.
> I setup the transactional replication to include an
> initialization of the Schema and Data, and asked the wizard to
> start the snapshot agent and begin the initialization process
> immediately.
> The Subscription creates and the snapshot is successful.
> However, the Push fails with the following error: "Unable to
> replicate a view or function because the referenced objects or
> columns are not present on the subscriber"
> Any help would be greatly appreciated!
>
> 1st time poster, I'm impressed with the knowledge of the
> board's members, but I'm new, so go easy on me if it's obvious
> to you :D
> EDIT: After looking through the replication, I have noticed
> that there are 3x as many tables in the Production database as
> in the replicated database, is this the snapshot not pulling
> properly, or implementing properly?
On a side note, I did try initially to do a snapshot replication of
this database (before I realized how large it was) and was told by the
wizard that it couldnt do more than 255 columns in a replication...
In my humble opinion thats a little limited considering who would be
doing replications, most of our databases are over 255 columns, is
that normal? Or is there any way you can get around that limit in
replication?
For instance this current database is 46,000 Rows, and about 3,500
Columns. The only way I can think of getting the correct schema &
data over (aside from a knowledgeable answer to my above post?) is
doing a backup & restore to the blank database. Would that work?
|||I suspect you are getting your error, because you are replicating some views
and not the base tables for these views. I would script out the view and
then run them in the subscriber to determine which views are missing the
base tables.
I believe you can only replicate tables less than 255 columns. In SQL 2005
the limit is 1000 column tables.
From BOL entitled Publishing data and database objects -
A table used in a snapshot or transactional publication can have a maximum
of 255 columns and a maximum row size of 8,000 bytes. A table used in a
merge publication can have a maximum of 246 columns and should have a
maximum row size of 6,000 bytes, because conflict-tracking columns may
consume up to 2,000 bytes. If row size exceeds 6,000 bytes in a merge
publication, conflict-tracking meta data may be truncated.
Horizontal, vertical, dynamic, and join filters enable you to create
partitions of data to be published. By filtering published
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
"SNA2" <DoNotEmail@.dbForumz.com> wrote in message
news:4_810668_841eddc87755ed7380f614344396c5b6@.dbf orumz.com...
> "SNA2" wrote:
> On a side note, I did try initially to do a snapshot replication of
> this database (before I realized how large it was) and was told by the
> wizard that it couldn't do more than 255 columns in a replication...
> In my humble opinion that's a little limited considering who would be
> doing replications, most of our databases are over 255 columns, is
> that normal? Or is there any way you can get around that limit in
> replication?
> For instance this current database is 46,000 Rows, and about 3,500
> Columns. The only way I can think of getting the correct schema &
> data over (aside from a knowledgeable answer to my above post?) is
> doing a backup & restore to the blank database. Would that work?
|||"Hilary Cotter3" wrote:
> I suspect you are getting your error, because you are
> replicating some views
> and not the base tables for these views. I would script out
> the view and
> then run them in the subscriber to determine which views are
> missing the
> base tables.
> I believe you can only replicate tables less than 255 columns.
> In SQL 2005
> the limit is 1000 column tables.
> From BOL entitled Publishing data and database objects -
> A table used in a snapshot or transactional publication can
> have a maximum
> of 255 columns and a maximum row size of 8,000 bytes. A table
> used in a
> merge publication can have a maximum of 246 columns and should
> have a
> maximum row size of 6,000 bytes, because conflict-tracking
> columns may
> consume up to 2,000 bytes. If row size exceeds 6,000 bytes in
> a merge
> publication, conflict-tracking meta data may be truncated.
> Horizontal, vertical, dynamic, and join filters enable you to
> create
> partitions of data to be published. By filtering published
>
> --
> 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
> "SNA2" <DoNotEmail@.dbForumz.com> wrote in message
> news:4_810668_841eddc87755ed7380f614344396c5b6@.dbf orumz.com...
> > > Hello;
> > >
> > > I have worked my way through numerous connection
> problems and
> > > login issues, but I'm left with one problem I'm
> hoping I can
> > > get some help on:
> > >
> > > I've got an SQL 2000 v 8.00.760 SP3a, with a
> production
> > > database I'm trying to replicate from one workgroup
> to another
> > > (yes, workgroups ack!) I've got the connections all
> worked
> > > out, it was a few days of fun...
> > >
> > > I've setup a transactional replication between a
> production
> > > database and a blank database.
> > > The database is about 1.5Gb so it's a hefty size.
> > > I setup the transactional replication to include an
> > > initialization of the Schema and Data, and asked
> the wizard to
> > > start the snapshot agent and begin the
> initialization process
> > > immediately.
> > > The Subscription creates and the snapshot is
> successful.
> > >
> > > However, the Push fails with the following error:
> "Unable to
> > > replicate a view or function because the referenced
> objects or
> > > columns are not present on the subscriber"
> > >
> > > Any help would be greatly appreciated!
> > >
> > >
> > > 1st time poster, I'm impressed with the knowledge
> of the
> > > board's members, but I'm new, so go easy on me if
> it's obvious
> > > to you :D
> > >
> > > EDIT: After looking through the replication, I have
> noticed
> > > that there are 3x as many tables in the Production
> database as
> > > in the replicated database, is this the snapshot
> not pulling
> > > properly, or implementing properly?
> replication of
> told by the
> replication...
> would be
> columns, is
> limit in
> 3,500
> schema &
> post?) is
> work?
Thank you Hillary! That was exactly the problem. [edit] I thought
Id post the solution I came up with and see if you had any input, see
below:
Im still in a bit of a bind with the Database, I understand that
replication, even with SQL 2005 will be an impossibility with the
database as it exists currently, we are looking at pearing it down and
streamlining it over the next year to bring down the size and the
number of columns, as it is quite a mess. We should be able to bring
it under 1000 columns, so replication can take place.
In the meantime, I have thought up a couple of scenarios to do simple
replication through backup/restore.
The full reason for the replication, is that we have a secure site for
the database, which is the reason for it residing on a workgroup, we
are looking to replicate it over the internet to another secure backup
location, so if (heaven forbid) our main secure site goes down, we can
flip the switch on the secondary location and have a working copy up
to date.
And no, Im not being paranoid, our so-called secure location went
down for a full hour recently... oh joy.
Anyway, the solution we came up with was doing a full backup weekly
and transferring the 1.5gb file over the link on an off-peak time, and
every 2 hours during the week, do a differential backup and send the
smaller differential backup file over as well. (the differential
backup is at the most 10-20mb)
THis gives us the option if something goes wrong at the primary
location to flip the switch and just run the quick differential
restore. It is unfortunately a manual process, so depending on the
timing and how quickly the issue is noticed, sometimes the flip may
take a while.
from SQL BOL I picked up the following example script and have
incorporated it successfully:
BACKUP DATABASE MyNwind
TO MyNwind_1
WITH INIT
GO
-- Wait
-- then create differential
BACKUP DATABASE MyNwind
TO MyNwind_2
WITH DIFFERENTIAL
GO
For ease of use, I have it setup through Enterprise Manager to run it
every 2 hours, however Im curious to find out if its possible to
time-stamp the filename within an SQL script like the above to create
a new file every time instead of writing over the old one? ie,
MyNwind_0624050230.bak or something similar?
THis solution seems to work well in place of the problem I had above
of trying to replicate a database that SQL has issues with if the
column count is too large.
Any thoughts? I was quite happy with this approach, as the
differential backup files are relatively small and transfer over the
net quickly enough as to not cause any bandwidth hogging. As I
mentioned, even after a week of changes with our database, the file
was only 10-20mb at the most. But, as pointed out, the way I have it
setup currently, SQL overwrites the file every time the differential
backup is done.
Thanks! Mucho appreciation.
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict233874.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=815727
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment