Replication between SQL 2005 Express Edition and SQL 2005 Standard Edition

Hello everybody,

I got a problem in my development phase. I'm developing a web-application system that running merge database technology which is centralized database and distributed database. This is because my customer want a highly optimum system standby.

My centralized database running SQL Server 2005 Standard Edition and the distributed database running SQL Server 2005 Express Edition (because it is free). Each of these database, the users can change the data on its database. So, every database must have the same data. from my understanding, each server must act as Publisher and Subscriber. Can SQL Server 2005 Express Edition be a Publisher?

If not, what the solution you all may suggest, because cost is the first factor. If my architecture is not correct, so what you all suggest to me to resolve the problem.

Thanks so much. Any suggestion and opinion is highly appreciate and I'm highly thanks to you all.

You cannot republish with SQL Express, you need Standard Edition or higher. As for solutions, see if you can try to not republish and use the single publisher scenario, or pay for Standard Edition. If cost is a factor, then you have to weigh $$$ versus not being able to meet your business requirements.|||If your intent is that you want the Standard and the express editions to be in sync, and that you want the DML that happen at the Express to be sent to the Standard edition too, you dont need to make the Express as a publisher. By default when you setup merge replication between 2 nodes, they both are read-write. That is Express site and do DML and the merge agent can send across these DML to the Standard edition site.

Replication based on Primary key, or Unique Key

Hi everybody,
I am looking to establish a merge replication, whereby the row is identified
by a unique key, than might be a combination of multile coloumns
Example If the inique id of a row is column1,column2
Whenever this row is changed in any of teh sites, the replication should
updates records having the same ID (column1, column2) In other sites.
Thanks
merge replication adds a unique key - a guid. So unless I am missing
something - merge replication will by default do what you require.
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
"zrod" <zrod@.aims-co.com> wrote in message
news:e9$EnScGFHA.2744@.tk2msftngp13.phx.gbl...
> Hi everybody,
> I am looking to establish a merge replication, whereby the row is
identified
> by a unique key, than might be a combination of multile coloumns
> Example If the inique id of a row is column1,column2
> Whenever this row is changed in any of teh sites, the replication should
> updates records having the same ID (column1, column2) In other sites.
> Thanks
>
|||Let's take the following example.
Site 1 and site 2 have the same row column 1=x, column 2= y, with a guid=g1
Site 1, delete the row and recreate it as column 1=x, column 2= y, it will
have a guid=g2
Before synchr happens,
Site 2 delete the row and recreate it as column 1=x, column 2= y, it will
have a guid=g3
Once the Data bases are synchronized we will have 2 rows having column 1=x,
column 2= y, guid=2, and guid-3
Zrod
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23dL4RgdGFHA.2524@.TK2MSFTNGP15.phx.gbl...
> merge replication adds a unique key - a guid. So unless I am missing
> something - merge replication will by default do what you require.
> --
> 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
> "zrod" <zrod@.aims-co.com> wrote in message
> news:e9$EnScGFHA.2744@.tk2msftngp13.phx.gbl...
> identified
>
|||Ok - let me get this straight you have a pk of col1 and col2. You whack it
at the publisher and subscriber, and then add it in again with the same
values.
What will happen is when the merge agent runs, you will get a pk collision.
By default the publisher's row will win, and the row on the subscriber will
be overwritten by the publisher's row. The conflict will be logged to the
conflict tables and will be visible using the conflict viewer.
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
"zrod" <zrod@.aims-co.com> wrote in message
news:%23590mteGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> Let's take the following example.
> Site 1 and site 2 have the same row column 1=x, column 2= y, with a
guid=g1
> Site 1, delete the row and recreate it as column 1=x, column 2= y, it will
> have a guid=g2
> Before synchr happens,
> Site 2 delete the row and recreate it as column 1=x, column 2= y, it will
> have a guid=g3
> Once the Data bases are synchronized we will have 2 rows having column
1=x,[vbcol=seagreen]
> column 2= y, guid=2, and guid-3
> Zrod
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23dL4RgdGFHA.2524@.TK2MSFTNGP15.phx.gbl...
should
>

Replication - sufficient bandwidth

Hi everybody,
are there any recommendations concerning the bandwidth given I want to
replicate a 4 GByte database?
We're using a 256k line (not exclusively) and the replication is breaking
down every time.
Any suggestion appriciated
Thanks
Peter
Dont know about recommendations... but what error are you getting?
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:A6CC1B26-D203-4E91-B4DB-6914E984D621@.microsoft.com...
> Hi everybody,
> are there any recommendations concerning the bandwidth given I want to
> replicate a 4 GByte database?
> We're using a 256k line (not exclusively) and the replication is breaking
> down every time.
> Any suggestion appriciated
> Thanks
> Peter
|||Hi Chris,
This is one
JOB RUN:'STA0015-ROSENSql-OLD0001-6' was run on 6/20/2005 at 6:45:00
AM
DURATION:0 hours, 6 minutes, 6 seconds
STATUS: Failed
MESSAGES:The job failed. The Job was invoked by Schedule 271
(Replication agent schedule.). The last step to run was step 3 (Detect
nonlogged agent shutdown.).
this is another error message
JOB RUN:'STA0015-ROSENSql-PER0001-24' was run on 4/26/2005 at
12:15:09 AM
DURATION:0 hours, 0 minutes, 2 seconds
STATUS: Failed
MESSAGES:The job failed. The Job was invoked by Schedule 341
(Replication agent schedule.). The last step to run was step 3 (Detect
nonlogged agent shutdown.).
seems there are timeouts, but we can figure out which screw to turn.
Database to large, lines to poor?
Peter
"ChrisR" wrote:

> Dont know about recommendations... but what error are you getting?
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:A6CC1B26-D203-4E91-B4DB-6914E984D621@.microsoft.com...
>
>

Replication

Hi everybody.
I am working on merge replication first time and I ran a
synchronizing processing and get error message:
Cannot insert the value NULL into column 'objid',
table 'ReplicationDB_1.dbo.sysmergeschemaarticles';
column does not allow nulls. INSERT fails.
My snapshots has created successfully.
Any suggestions or advices?
Thank you Mike.
I believe this is a bug. Contact PSS for a hot fix.
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:8bbf01c432aa$26749460$a001280a@.phx.gbl...
> Hi everybody.
> I am working on merge replication first time and I ran a
> synchronizing processing and get error message:
> Cannot insert the value NULL into column 'objid',
> table 'ReplicationDB_1.dbo.sysmergeschemaarticles';
> column does not allow nulls. INSERT fails.
> My snapshots has created successfully.
> Any suggestions or advices?
> Thank you Mike.
>
>

Replication

Hi everybody,
Could someone explain me how to use replication in sql server with access as
frontend?This is such a large topic, I would suggest you begin by reading and
following the directions in Books onLine ( the SQL Server Help files..)
THere is enough information there to get you started...
"Nicollo Machiaveli" <machiaveli280278@.hotmail.com> wrote in message
news:eR%23IffTcDHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi everybody,
> Could someone explain me how to use replication in sql server with access
as
> frontend?
>

replication

Hi everybody,

Could you please advise me what would be the best method to synchronize
MSSQL data in the following scenario:

SiteA (live site) -- T1 link -- SiteB (redundant site)

Each site consists of web server and MSSQL 2005 server. SiteA is the
site that my customers normally use when accessing my store. SiteB is
the backup site to which customers will be directed, should something
happen to Internet connectivity of siteA. When the failover occurs,
client request are pointing to my redundant web servers within a minute
(this happens automatically, as I am using the hosted DNS service). I
was wondering, what is the best method of keeping the database at siteB
up to date with all the transactions that took place at siteA? I do not
want my customers to access the redundant database if it does not
contain all the transactions. What would you advise?

Thanks,
ALThe best place to ask this is microsoft.public.sqlserver.replication.

I am no replication expert, but your requirement sounds like it needs
Database Mirroring. I am guessing that a T1 would be a bit slow for
synchronous mode, also known as high-safety mode. "Under high-safety
mode, when a session begins, the mirror server synchronizes the mirror
database with the principal database as quickly as possible. Once the
databases are synchronized a committed transaction is committed on
both partners, at the cost of increased transaction latency."

That would leave asynchronous operation mode, also known as
high-performance mode. "The mirror server attempts to keep up with
the log records sent by the principal server. The mirror database
might lag somewhat behind the principal database, though, typically,
the gap between the databases is small. However, the gap can become
substantial if the principal server is under a heavy work load or the
system of the mirror server is over loaded."

"In high-performance mode, as soon as the principal server sends a log
record to the mirror server, the principal server sends a confirmation
to the client, without waiting for an acknowledgement from the mirror
server. This means that transactions commit without waiting for the
mirror server to write the log to disk. Such asynchronous operation
permits the principal server to run with minimum transaction latency,
at the risk of some potential data loss."

The quotes are straight from BOL.

Roy Harvey
Beacon Falls, CT

On Sat, 29 Sep 2007 13:38:48 -0400, "aleu@.vp.pl" <aleu@.vp.plwrote:

Quote:

Originally Posted by

>Hi everybody,
>
>Could you please advise me what would be the best method to synchronize
>MSSQL data in the following scenario:
>
>SiteA (live site) -- T1 link -- SiteB (redundant site)
>
>Each site consists of web server and MSSQL 2005 server. SiteA is the
>site that my customers normally use when accessing my store. SiteB is
>the backup site to which customers will be directed, should something
>happen to Internet connectivity of siteA. When the failover occurs,
>client request are pointing to my redundant web servers within a minute
>(this happens automatically, as I am using the hosted DNS service). I
>was wondering, what is the best method of keeping the database at siteB
>up to date with all the transactions that took place at siteA? I do not
>want my customers to access the redundant database if it does not
>contain all the transactions. What would you advise?
>
>Thanks,
>AL

|||Roy Harvey (SQL Server MVP) wrote:

Quote:

Originally Posted by

The best place to ask this is microsoft.public.sqlserver.replication.
>
I am no replication expert, but your requirement sounds like it needs
Database Mirroring. I am guessing that a T1 would be a bit slow for
synchronous mode, also known as high-safety mode. "Under high-safety
mode, when a session begins, the mirror server synchronizes the mirror
database with the principal database as quickly as possible. Once the
databases are synchronized a committed transaction is committed on
both partners, at the cost of increased transaction latency."
>
That would leave asynchronous operation mode, also known as
high-performance mode. "The mirror server attempts to keep up with
the log records sent by the principal server. The mirror database
might lag somewhat behind the principal database, though, typically,
the gap between the databases is small. However, the gap can become
substantial if the principal server is under a heavy work load or the
system of the mirror server is over loaded."
>
"In high-performance mode, as soon as the principal server sends a log
record to the mirror server, the principal server sends a confirmation
to the client, without waiting for an acknowledgement from the mirror
server. This means that transactions commit without waiting for the
mirror server to write the log to disk. Such asynchronous operation
permits the principal server to run with minimum transaction latency,
at the risk of some potential data loss."
>
The quotes are straight from BOL.

Roy,

Thanks for the provided information. Is there any information on how
much bandwidth is required to perform the synchronous mode successfully
(I was considering purchasing second T1 link for redundancy anyway)?
Would this be sufficient? What happens in both synchronous and
asynchronous cases, when you loose the connectivity between the two
databases lets say for 10 minutes?

Could you please provide me with some links on MSSQL 2005 mirroring
options/requirements/best practices?

Thanks,
AL|||On Sat, 29 Sep 2007 22:46:44 -0400, "aleu@.vp.pl" <aleu@.vp.plwrote:

Quote:

Originally Posted by

>Thanks for the provided information. Is there any information on how
>much bandwidth is required to perform the synchronous mode successfully
>(I was considering purchasing second T1 link for redundancy anyway)?
>Would this be sufficient? What happens in both synchronous and
>asynchronous cases, when you loose the connectivity between the two
>databases lets say for 10 minutes?
>
>Could you please provide me with some links on MSSQL 2005 mirroring
>options/requirements/best practices?
>
>Thanks,
>AL

If you loose connectivity for ten minutes it would have two impacts.
One, once the connection is re-established there is an additional
delay while the backlog is cleared. Two, you could not clear the
transactions from the log on the source system until after the
connection is re-established and the backlog cleared.

I don't have the hands-on experience with mirroring to have a good
feel for bandwidth requirements, but the major variable is the level
and nature of database activity that only you know. Reading a bit
more from some googled items I am more and more thinking you would be
restricted to asynchronous. If you take a look at your transaction
logs they should give you some idea how much data has to be pushed
over the link.

I suggest googling sql server 2005 mirroring and reading up. And
hoping someone with hands-on experience joins the discussion!

Roy Harvey
Beacon Falls, CT|||Roy Harvey (SQL Server MVP) wrote:

Quote:

Originally Posted by

If you loose connectivity for ten minutes it would have two impacts.
One, once the connection is re-established there is an additional
delay while the backlog is cleared. Two, you could not clear the
transactions from the log on the source system until after the
connection is re-established and the backlog cleared.
>
I don't have the hands-on experience with mirroring to have a good
feel for bandwidth requirements, but the major variable is the level
and nature of database activity that only you know. Reading a bit
more from some googled items I am more and more thinking you would be
restricted to asynchronous. If you take a look at your transaction
logs they should give you some idea how much data has to be pushed
over the link.
>
I suggest googling sql server 2005 mirroring and reading up. And
hoping someone with hands-on experience joins the discussion!
>

Thanks Roy. It is time to do some serious reading.

Regards,
AL