Can replication be ran on a MS sharepoint database?

Hello,

I was curious if you can have merge and/or snapshot replication setup on a MS sharepoint database? Thanks.

John

I don't think that those will work for the following reason.

At a recent MS course, we asked our instructor if it was possible to change the content database in SP central Admin to a secondary database server during a crisis. They advised that this might be a problem because the SP configuration database creates hard-coded entries referencing the database server name that it resides on. They suspsected that these entries would cause problems in the long run, if not right away.

We decided to test this out by creating a virtual environment that mimicked our production system. We then copied the database to a seperate physical machine. To try and avoid the configuration database problems, we created a new virtual site on the VM that mirrored our existing front end and pointed it to the new database to recreate the configuration database.

Everything worked okay for about 3 hours then we began to see timeouts due to maxpoolsize being reached. When we checked the event log we also found that the server was STILL trying to connect to the original database even though everything was on the new server. Eventually, it became impossible to access any of the sites in the virtual environment.

I imagine that these entries in the configuration database would cause the same problem if we were to replicate from one server to another. We're now thinking of using log shipping or clustering to try and provide the redundancy we're looking for.

|||

Jaydog,

Thanks for your reply.

John

|||Hey John,

Looks like I was premature with my first response. We tried the same thing one more time, except that we shut down the old SQL Server before clearing the old configuration database and allowing SharePoint to rebuild it. So far, it looks like everything is up and running.

We're giving it a few days to see if there are any adverse effects and then we will be trying to get merge replication up and running. I'll post again once there is more to report.|||

Jaydog,

I look forward to what you find out.

John

|||

Jaydog,

I was curious if you had an update on this?

John

|||

Jaydog,

I would also be interested in finding out how that went.

JBW

Can replication be ran on a MS sharepoint database?

Hello,

I was curious if you can have merge and/or snapshot replication setup on a MS sharepoint database? Thanks.

John

I don't think that those will work for the following reason.

At a recent MS course, we asked our instructor if it was possible to change the content database in SP central Admin to a secondary database server during a crisis. They advised that this might be a problem because the SP configuration database creates hard-coded entries referencing the database server name that it resides on. They suspsected that these entries would cause problems in the long run, if not right away.

We decided to test this out by creating a virtual environment that mimicked our production system. We then copied the database to a seperate physical machine. To try and avoid the configuration database problems, we created a new virtual site on the VM that mirrored our existing front end and pointed it to the new database to recreate the configuration database.

Everything worked okay for about 3 hours then we began to see timeouts due to maxpoolsize being reached. When we checked the event log we also found that the server was STILL trying to connect to the original database even though everything was on the new server. Eventually, it became impossible to access any of the sites in the virtual environment.

I imagine that these entries in the configuration database would cause the same problem if we were to replicate from one server to another. We're now thinking of using log shipping or clustering to try and provide the redundancy we're looking for.

|||

Jaydog,

Thanks for your reply.

John

|||Hey John,

Looks like I was premature with my first response. We tried the same thing one more time, except that we shut down the old SQL Server before clearing the old configuration database and allowing SharePoint to rebuild it. So far, it looks like everything is up and running.

We're giving it a few days to see if there are any adverse effects and then we will be trying to get merge replication up and running. I'll post again once there is more to report.
|||

Jaydog,

I look forward to what you find out.

John

|||

Jaydog,

I was curious if you had an update on this?

John

|||

Jaydog,

I would also be interested in finding out how that went.

JBW

Can replication be ran on a MS sharepoint database?

Hello,

I was curious if you can have merge and/or snapshot replication setup on a MS sharepoint database? Thanks.

John

I don't think that those will work for the following reason.

At a recent MS course, we asked our instructor if it was possible to change the content database in SP central Admin to a secondary database server during a crisis. They advised that this might be a problem because the SP configuration database creates hard-coded entries referencing the database server name that it resides on. They suspsected that these entries would cause problems in the long run, if not right away.

We decided to test this out by creating a virtual environment that mimicked our production system. We then copied the database to a seperate physical machine. To try and avoid the configuration database problems, we created a new virtual site on the VM that mirrored our existing front end and pointed it to the new database to recreate the configuration database.

Everything worked okay for about 3 hours then we began to see timeouts due to maxpoolsize being reached. When we checked the event log we also found that the server was STILL trying to connect to the original database even though everything was on the new server. Eventually, it became impossible to access any of the sites in the virtual environment.

I imagine that these entries in the configuration database would cause the same problem if we were to replicate from one server to another. We're now thinking of using log shipping or clustering to try and provide the redundancy we're looking for.

|||

Jaydog,

Thanks for your reply.

John

|||Hey John,

Looks like I was premature with my first response. We tried the same thing one more time, except that we shut down the old SQL Server before clearing the old configuration database and allowing SharePoint to rebuild it. So far, it looks like everything is up and running.

We're giving it a few days to see if there are any adverse effects and then we will be trying to get merge replication up and running. I'll post again once there is more to report.|||

Jaydog,

I look forward to what you find out.

John

|||

Jaydog,

I was curious if you had an update on this?

John

|||

Jaydog,

I would also be interested in finding out how that went.

JBW

Can not see any server in the available servers list

I successfully installed SQL Server 2005 Express edition in my pc. I ran Visual Basic 6 and used and Ado object to build a connection string to access the server but, I could not see any server in the comobox list.

I always get connection failure messages say that the server does not exitst!!!

Any help please.

Thanks

The installation process does not leave the server open to 'outside' connections. You will have to manually 'open' the server. Here are some useful resources:

Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

Configuration -Connect to SQL Express and ‘Stay Connected’
http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html

can not remove rowguid currently replicated

After losing my publishing server, I re-built on a new server.
The databases had replication remnants so I ran cleanup scripts
(Replication system Objects, indexes and rowguids)
I then re-built replication
I find that I have a column (rowguid) left over that I can not remove.
The error message is "Can not Alter table, ... rowguid currently replicated"
how do I remove this column?
Haven't seen this error personally, but please have a look at the solutions
proposed in this thread for some ideas to take advantage of:
http://www.webservertalk.com/showthread.php?t=902849
HTH,
Paul Ibison
|||Hi Paul;
This is what I have done so far
Problem:
Open EM
Open Agency Table
Delete Column
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
ALTER TABLE DROP COLUMN failed because 'rowguid' is currently replicated.
Approaches:
Drop replicated column - sp_repldropcolumn
Drop subscription - sp_dropsubscription
drop publication - sp_droppublication
disable publishing and distributor - sp_removedbreplication
exec sp_repldropcolumn @.source_object = 'Agency'
, @.column = 'rowguid'
, @.force_reinit_subscription = 1
Server: Msg 21246, Level 16, State 1, Procedure sp_repldropcolumn, Line 212
This step failed because table 'Agency' is not part of any publication.
sp_dropsubscription @.publication = 'IsoprepArchive'
, @.subscriber = 'SQLDEV'
, @.destination_db = 'Archivedisopreps'
sp_droppublication @.publication = 'IsoprepArchive'
exec sp_removedbreplication 'Isoprep'
-- Unmark table for replication
SELECT 'exec sp_MSUnmarkReplInfo ' + '''' + Name + '''' + Char(13)
+ ' GO ' + Char(13)
+ 'ALTER TABLE ' + Name + CHAR(13)
+ 'DROP CONSTRAINT DF_' + Name + '_rowguid' + Char(13)
+ ' GO ' + CHAR(13)
+ 'ALTER TABLE ' + Name + CHAR(13)
+ 'DROP COLUMN ROWGUID' + Char(13)
+ ' GO ' + Char(13) + Char(13)
FROM sysobjects
WHERE xtype = 'U'
-- return replinfo flag
SELECT 'Print ' + '''' + Name + '''' + Char(13) + ' GO '
+ Char(13)
+ 'SELECT replinfo' + Char(13)
+ 'FROM sysobjects' + Char(13)
+ ' WHERE name = ' + '''' + name + '''' + Char(13)
FROM sysobjects
WHERE xtype = 'U'

exec sp_MSUnmarkReplInfo 'ErrorLog'
GO
ALTER TABLE ErrorLog
DROP CONSTRAINT DF_ErrorLog_rowguid
GO
ALTER TABLE ErrorLog
DROP COLUMN ROWGUID
GO
Warning: The table 'ErrorLog' has been created but its maximum row size
(9416)
exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes.
Server: Msg 4932, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because 'ROWGUID' is currently replicated.
Warning: The table 'ErrorLog' has been created but its maximum row size
(9416)
exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes.
Thanks
MJS
"Paul Ibison" wrote:

> Haven't seen this error personally, but please have a look at the solutions
> proposed in this thread for some ideas to take advantage of:
> http://www.webservertalk.com/showthread.php?t=902849
> HTH,
> Paul Ibison
>
|||Quite By Accident, I found the following:
open EM
Open Design view of table
open constraint tab
Look for replication constraints
I found a constraint that looked like:
"repl_identity_range_sub_B0BB3703_3C1D_4648_9DCA_B F47DE69E485"
execute the following to build an alter table statement that will
remove the constraints.
SELECT 'ALTER TABLE ' + U.name + CHAR(13)
+ 'DROP CONSTRAINT ' + C.name + CHAR(13)
+ CHAR(13) + 'GO' + CHAR(13)
FROM sysobjects C
, sysobjects U
WHERE C.parent_Obj = U.Id
AND C.xtype = 'C'
AND C.name like 'repl_identity_range_sub_%'
Thanks
MJ
"mj" wrote:
[vbcol=seagreen]
> Hi Paul;
> This is what I have done so far
> Problem:
> Open EM
> Open Agency Table
> Delete Column
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
> ALTER TABLE DROP COLUMN failed because 'rowguid' is currently replicated.
> Approaches:
> Drop replicated column - sp_repldropcolumn
> Drop subscription - sp_dropsubscription
> drop publication - sp_droppublication
> disable publishing and distributor - sp_removedbreplication
>
> exec sp_repldropcolumn @.source_object = 'Agency'
> , @.column = 'rowguid'
> , @.force_reinit_subscription = 1
> Server: Msg 21246, Level 16, State 1, Procedure sp_repldropcolumn, Line 212
> This step failed because table 'Agency' is not part of any publication.
> sp_dropsubscription @.publication = 'IsoprepArchive'
> , @.subscriber = 'SQLDEV'
> , @.destination_db = 'Archivedisopreps'
> sp_droppublication @.publication = 'IsoprepArchive'
> exec sp_removedbreplication 'Isoprep'
> -- Unmark table for replication
> SELECT 'exec sp_MSUnmarkReplInfo ' + '''' + Name + '''' + Char(13)
> + ' GO ' + Char(13)
> + 'ALTER TABLE ' + Name + CHAR(13)
> + 'DROP CONSTRAINT DF_' + Name + '_rowguid' + Char(13)
> + ' GO ' + CHAR(13)
> + 'ALTER TABLE ' + Name + CHAR(13)
> + 'DROP COLUMN ROWGUID' + Char(13)
> + ' GO ' + Char(13) + Char(13)
> FROM sysobjects
> WHERE xtype = 'U'
> -- return replinfo flag
> SELECT 'Print ' + '''' + Name + '''' + Char(13) + ' GO '
> + Char(13)
> + 'SELECT replinfo' + Char(13)
> + 'FROM sysobjects' + Char(13)
> + ' WHERE name = ' + '''' + name + '''' + Char(13)
> FROM sysobjects
> WHERE xtype = 'U'
>
> ----
> exec sp_MSUnmarkReplInfo 'ErrorLog'
> GO
> ALTER TABLE ErrorLog
> DROP CONSTRAINT DF_ErrorLog_rowguid
> GO
> ALTER TABLE ErrorLog
> DROP COLUMN ROWGUID
> GO
> Warning: The table 'ErrorLog' has been created but its maximum row size
> (9416)
> exceeds the maximum number of bytes per row (8060).
> INSERT or UPDATE of a row in this table will fail if the resulting row
> length exceeds 8060 bytes.
> Server: Msg 4932, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN failed because 'ROWGUID' is currently replicated.
> Warning: The table 'ErrorLog' has been created but its maximum row size
> (9416)
> exceeds the maximum number of bytes per row (8060).
> INSERT or UPDATE of a row in this table will fail if the resulting row
> length exceeds 8060 bytes.
>
> Thanks
> MJS
> ----
> "Paul Ibison" wrote: