I would appreciate if someone could help me with the following problem:
We use two SQL Server 2008 R2 databases under transactional replication: transactional publication with updatable subscriptions. because we run out of disk space we need to move the database files into a new drive. But I don't want to break the replication.
What I'm looking for are the required steps that will help me to move the files to the new drive.
Thanks
On a test rig, I'm trying to get SQL Server 2005 peer to peer (transactional) replication working, with the replication agents running under the NT AUTHORITY\NETWORK SERVICE account.
Before this I setup a peer to peer replication with the agents running under the SQL Server agent service account (i.e. SYSTEM account) and I created a security group in AD containing the machine accounts. I gave this group the neccessary permissions on the dbs, PAL, distributor. This worked fine.
If I try to do the exact same setup with the agents running as NT AUTHORITY\NETWORK SERVICE (or NT AUTHORITY\NETWORKSERVICE - no space, I tried both), I see the following error in replication monitor:
Unable to start execution (reason: Error authenticating proxy Nt Authority\Network Service, system error: Logon Failure: unknown username or bad password)
I have also tried making Network Service a sql login on each server, and giving it the sysadmin role. This made no difference. Is it simply not possible to run a replication agent as network service?
(note - I am aware I can use an AD account with a username/password. For various reasons we don't want to do that in this instance)
We are upgrading our ERP application to run on SQL Server 2008 R2. It currently runs as a file-based system in it's own Server 2008 virtual machine. SQL Server will be installed into that VM.
We have a separate virtual machine (on the same physical host) that runs SQL Server for other data. Is there a reason (performance or otherwise) to go through the trouble of replicating data from the ERP SQL server to another VM? My original plan was to replicate for reporting purposes and reduce the load on the ERP SQL server, but if both SQL servers are on the same physical host, I can't see a performance benefit to doing so.
I have a partitioned table that is published for transactional replication. The SWITCH operations on this table appears to replicated to the subscriber even though the options to allow_partition_switch = 1 and replicate_partition_switch = 0 on the publication.
When I try to execute the following sample script on the publisher ...
ALTER TABLE [PartitionedTable] SWITCH PARTITION 21 TO [PartitionedTable_SWAP] PARTITION 21
The following error is returned, and a stack dump is generated.
A user request from the session with SPID 53 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
Dropping the publication allows the SWITCH PARTITION statement to execute as expected. However, why is transaction replication clearly either trying to replicate the SWITCH operation, or at the very least, is dependent upon the SWITCH operation occurrence on the publisher? Is this a SQL Server bug? (Maybe fixed in a later SP or CU release??)
Another question is if I were to apply a filter on the article, would this resolve the issue?
Sean Fitzgerald
I have a updatable transactional replication set with SQL Server 2008. Everything is working fine.
I added a new table to the existing publication through sp_addarticle followed by sp_addsubscription. After that I ran the Snapshot Agent. The snapshot has been generated only for newly added table. So the new table was successfully replicated to subscriber. I could even able to replicate a newly inserted record into new table to subscriber.
But it's not possible viceversa. When I insert a record into new table in the subscriber database, I am getting an error
Msg 515 'Cannot insert the value NULL into column 'msrepl_tran_version', table Servername.dbo.Tablename'; column does not allow nulls. INSERT fails.'.
Please help me to resolve this issue.
Many thanks in advance.
Geeta