Wednesday, March 20, 2013

[MS SQL Server] Replication configuration

[MS SQL Server] Replication configuration


Replication configuration

Posted: 20 Mar 2013 05:02 AM PDT

We have one requirement in Tranactional replication setup, One publisher and three subscriber.. How to configure one pub and three subscriber, and how to schedule data transfer for all subscriber.. Please anyone help to me ... Thanksjerry

Capturing deadlock info

Posted: 19 Mar 2013 11:56 PM PDT

I am looking for advice on capturing deadlock info.Can somebody please address the follwoing points :1. Does enabling trace flags T1204 AND T1222 have any impact on server performance and do i need to restart the sql server service to do this as the boss has said this really isnt an option.2. If i run profiler i understand it will have to be running at the time a deadlock occurs to capture info - what are the events i need to capture ? - Is there anyother way to capture the info - for example is a server side trace a better option ?3.Any links to good advice on troubleshooting deadlocks would be appreciated.Many thanksSteve:-)

SSIS package failing while executing as Agent job

Posted: 20 Mar 2013 01:29 AM PDT

Hi,I had some weird issues while running SSIS package as agent job. I can run SSIS package manually but failing to run as agent job. I thought its permission issues with Agent service account then I RDP into the server using service account login (domain\agentserviceaccount) and run the package and it went fine. any idea? much appreciated :)Thanks

SQL Agent - SQLSTATE 21000 error

Posted: 10 Jan 2010 08:14 PM PST

I have found weird error in my SQL Agent which is produced about every 30 seconds. The message reads as follows[298] SQLServer Error: 512, Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [SQLSTATE 21000]Strange thing is I cannot find the process which is causing this error, I have even disabled all SQL Agent jobs and restarted the service and server eventually but it still produces this error message every 30 seconds. Anyone come across this before?

CLR Question

Posted: 19 Mar 2013 09:37 AM PDT

We have a production CLR process that queries Active Directory for User Information and loads it into a table. We had 2 jobs that collided and ended up with blocking and hung jobs accessing the table. In an attempt to stop and restart the process I killed the job, however the process would not rollback as I believe the CLR thread still had a lock on the table. The process was in a rollback state and never moving(this is a small transaction with no more than 100 or so rows)I know this is not an ideal situation, but was curious for opinions on how to get rid of the lock on the table:My understanding is you can use the kpid from the sysprocesses table and map to a process on the server, and kill it there, but you run a likely risk of crashing SQL Server completely.I was thinking the most likely way to clear the process is to restart the SQL Service, however this would have been a large impact to the other databases on the instance.My thought and what I was looking for feedback on was switching the clr enabled function from 1 to 0 and then back to 1. My thought was this would unload the assemblies and kill the process. There was minimal impact as we only have a few databases using CLR assemblies compared to restarting the entire service. Thanks for the feedbackErich

Log shipping and CDC

Posted: 19 Mar 2013 10:41 PM PDT

I come seeking the wisdom of the collective. I have searched to no avail to try and understand the implication of using CDC on a database that uses log shipping. In a weird way I also want to understand if CDC will affect log shipping and vice versa.After much head scratching I am assuming that the truncation point in the log will move if CDC is deployed. So instead of the last commited transaction it will be the last commited transaction that has been harvested?So if I have log shipping every 10 minutes does this mean I need to get the CDC data out of my source system before that 10 minutes cut off. Will it make my log shipping out of date??So many questions. Pointers to good articles gratefully received.Many thanks:w00t:

Doubts on mirroring

Posted: 19 Mar 2013 06:09 PM PDT

Dear AllI need some clarifications on mirroring. please help me out1) After configure the mirroring if the principal is failed and don't have the witness?if witness is there automatically failover will happen and mirror will come onlineif witness is not there how to do the fail over2) if mirror server is fail here no issue for principal after some time principal is also not available how to do the fail overCan anyone please help on this

how to know the delta data volume processed every day in mb

Posted: 07 Mar 2013 05:57 PM PST

Hi I wanted to know if there is a way to figure out how many bytes of data have been processed while performing the delta on a table. I can easily get the delta count of every delta job run, but I'm not aware if there is a way to figure out how many bytes are processed based on the delta row counts or other. We use Informatica for our ETLs if this helps to get the delta data volume info.Please help and suggest of all possible ways to get this info.

Log Shipping Compression

Posted: 19 Mar 2013 09:14 AM PDT

Hello, does anybody know if any versions of SQL have the ability to compress log files before shipping. The plan was to log ship from the local data centre to a SQL instance in EC2 (Amazon Cloud) but due to the high latency we get from here (NZ) to Amazon (Singapore) a large file takes an unrealistic time to copy. Therefore compression would be a help.We are currently using SQL 2008 STD.I think I have read the SQL 2008 Enterprise editions have this feature but I am unsure if SQL 2008 R2 STD or SQL 2012 STD also have this feature as I think normal backup compression was added to these additions....thanks

Auditing a Table for Select Statement By Active Directory User

Posted: 19 Mar 2013 08:10 AM PDT

Hey guys,I'm attempting to set up auditing on a table in my database. Under the database audit specification properties GUI, I have Audit Action Type "Select"Object Class "Object"Object Schema "dbo"Object Name "ImportantTable"Principal Name "OurNetworkDomain\Me"With this current setup, I perform a Select * From ImportantTableHowever the audit log does no logging. If I change the principal name to "Public" or "dbo" it correctly logs my activity and even shows under "Session Server Principal Name" and "Server Principal Name" columns that I am coming from OurNetworkDomain\MeI don't want to use dbo or Public as it will collect way too much information. Any ideas why this doesn't work when I just specify the Active Directory user individually?Thanks!Andrew

No comments:

Post a Comment

Search This Blog