Friday, May 31, 2013

[MS SQL Server] Transactional Push Replication - Overhead of having Distributor on the Publisher

[MS SQL Server] Transactional Push Replication - Overhead of having Distributor on the Publisher

Transactional Push Replication - Overhead of having Distributor on the Publisher

Posted: 31 May 2013 12:21 AM PDT

I've recently set up Transactional Replication with Push subscriptions. At the moment the distributor is running on the publisher server. I've been getting vague reports of "it's going slow since we put replication on". I'm aware that running the distributor on the publisher server has an overhead, but I can't find any specific information on what that overhead is. I'd imagine that it's an amount of I/O as it writes the transactions into the distribution database, plus a bit of CPU? Weirdly, the reports are that the published database is running slowly, but other databases on the server (and on the same disk) are running fine. I'm a little skeptical to honest as I'd have thought extra I/O or CPU load would affect the whole server, rather than one specific database.If it turns out that it [i]is[/i] causing issues, what are my options?- I've ruled out Pull subscriptions because we're replicating to SQL Server Express, which doesn't have SQL Server Agent to run the jobs.- It would be a very hard sell to convince the customer to buy another server to run the distributor on.- Would it be worth adding a new disk to put the distribution database on, but still running the distributor on the publisher?I realise that this post is lacking specific information, but at this stage I'm just looking at what my options might be. I'm a developer who does a bit of DBA-ing and I'm bit new to replication, so I understand the basics but the minutiae are a bit of a mystery to me! Any help you could provide would be most appreciated. Thanks in advance

Database just says restoring...for last 20 hours?!

Posted: 13 Jan 2010 12:00 AM PST

Hello,I've got a SQL Server 2005 server that I have remote access onto as well as using SSMS.I've got a SQL Server 2008 server that I don't have remote access onto and only can use SSMS.I need to move a database from the old 2005 box to the new 2008 box.I did a backup to our Backup server (across network). That went OK.Then I needed to get the backup to the new server, but I can't just remote it and copy from the backup server.I tried a restore backup with move. That seemed to work. It said it was restoring. That was 20 hours ago. The database is a small database. 1) How do I stop it? I tried taking it offline. It gave me an error message saying that it was busy restoring the database.2) How do you restore a database through TSQL from a network server? I thought the restore "WITH MOVE" was the right way.Thanks!

DB Randomly sets in Single User Mode

Posted: 30 May 2013 10:37 PM PDT

I have a database that on at least 2 occasions randomly went into single user mode.It was not immediately after a backup - I don't see any alter database statements and I find no comments in syscommentsIs there any idea why it does/did this and where else I can look? Please advise

VLFs sizes

Posted: 30 May 2013 09:42 AM PDT

Hi All,I want to see someone's explanation if there is a performance loss (and why in case of) when some VLFs are small and some are big, in a transaction log.For example a database had had small growths of 128MB, and each VLF with 16MB; and then a change of 4608MB is set for the growth, and each new VLF is now 288MB.I know how to make the VLFs all equal. It will be good posting some links where this issue is discussed, too.Thank you,IgorMi

DB Backup Maintenance Plan Won't Execute

Posted: 31 Jul 2012 08:22 AM PDT

I've created a back up plan on one DB using the Maintenance Plans Wizard and the regular way (New Maintenance Plans). Everything looks to be setup correctly but when I try to execute the plan to see if it'll run properly, the plan fails to execute giving me an "Execution failed" message. Looking at the SQL Server Agent job history it seems like my SQL Server can't determine if my Windows NT group/User ID has the appropriate server access. I'm able to do basically everything I want to inside the server but execute the maintenance plan. Does any body know how to change my server access so I can get my maintenance plans running?

SQL 2008 Audit doesn't show parameters

Posted: 05 Sep 2009 03:54 AM PDT

I need to audit all accesses, updates, inserts, deletes, executes, etc. done to specific tables in our database. I upgraded my SQL Server to 2008 so I could use the new Audit functionality. This works great and shows me everything I need and who accessed it. The problem is I don't know what the query contained. Let me give some examples:If I run a query from the Management studio it gives me the following:Query that was run: Select * From Customer Where ID = '123'Audit shows this: Select * From Customer Where ID = '123' This is what I would expect all the time. But if I add in a parameter it gives the following:Query that was run: Declare @id varchar(255) Set @id = '123' Select * From Customer Where ID = @idAudit show this: Select * From Customer Where ID = @idI would like to be able to see what the parameter was in case I need to investigate something.Does anyone have any suggestions? Our application sends in thousands of stored procedures with parameters and how it currently works won't help.Thanks in advance!JN
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog