Thursday, May 9, 2013

[MS SQL Server] EMC RecoverPoint and SQL Server

[MS SQL Server] EMC RecoverPoint and SQL Server

EMC RecoverPoint and SQL Server

Posted: 16 Jul 2012 09:16 PM PDT

We are trying to implement EMC RecoverPoint with SQL clustering.What we had to do at the DR side after syncing the databases was detach the databases and take the clustered disk resources off-line so replication could happen at the block-level. However when we brought the DR side back on-line we had to manually attach the databases.My question is - is this the right way of doing it as ideally we want to have this automated and not have to manually attach the databases t the DR side.Thanks.

How can I kill ad-hoc or long time running queries, safely?

Posted: 09 May 2013 02:39 AM PDT

Ok,Need the final push on this ...I do have a requirement at work that I need to control or kill ad-hoc queries that have been affecting performance on an specific client or database. I would prefer to avoid the bad queries altogether, but I can't. And the reason why this is happening in the 1st place is because the developers had the idea (not sure if good or bad) of giving the flexibility to the user of writing their own customize reports. Nice, user or business perspective, but now I have the database problem on my hands. Anyway ...Narrowed the problem to this T-SQL query that allows me to see what's taking more than X amount of time (sharing, in case someone else need it ) ...[code="sql"]SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT TOP 1 [Spid] = session_Id , [Database] = DB_NAME(sp.dbid) , [User] = nt_username ,total_elapsed_time/1000 AS 'elapsed time in seconds' FROM sys.dm_exec_requests er INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt WHERE session_Id > 50 AND DB_NAME(sp.dbid)='MyDatabaseHere' AND session_Id NOT IN (@@SPID) AND nt_username='Joe' ORDER BY total_elapsed_time/1000 DESC;[/code]The question I have ... how to handle the 2nd part, which is killing the process. I guess that I can put above in a job, insert on a temp table, and query for anything longer than 120 seconds and then kill? However, I do not want to end with bunch of rollback processes if the rogue transaction is an INSERT, UPDATE, DELETE. Then I will affect performance instead of improving it.By the way, I also thought about using query governor, but I'm afraid that will affect the whole SQL instance. I need this for a particular SQL login and specific client or database.Any hints?

Change SQL Notification subject

Posted: 08 May 2013 07:59 AM PDT

Hi All,SQL Server Job System: 'TESTJOB' completed on \\Server_NameI wanted to see if I can modify this to send e-mails with this subject line.SQL Server Job System: 'TESTJOB' completed on <Server_Name>Is this possible? If so can someone help me with?Thanks in Advance.

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

Determine who/what caused large tempdb growth

Posted: 08 May 2013 11:46 PM PDT

Hi,My shop is running 2008R2 SP2. Our monitoring tool indicated large tempdb data file growth over a short period, then later showed over 90% free space. We are trying to determine who / what caused the growth to determine if it is likely to be regular (job-based), or one-time (bulk load).I've tried using dynamic management views/functions (dm_xe_*) as well as a bit of extended events (event databases_data_file_size_changed), but can't seem to come up with exactly what I want.If anyone knows a method to do this, or can help me with one of the methods I've tried, I'd appreciate it.~ Jeff

Plan_Guide not working for cross DB queries

Posted: 08 May 2013 12:12 PM PDT

Investigation on longer running queries has shown high compile times for some adhoc queries sent from a front end app.I have added Plan Guides and they seem to work for all but one query.This query is a cross DB join (on the same server) but does not seem to want to use the Plan Guide.The query is like this:[i]SELECT SUM(QUANTITY) AS COUNTERFROM vw_TRANS_LINE_CATEGORY a ,vw_DLG_Participants cWHERE QUANTITY > 0 AND a.IS_USEDUP != 1 AND a.TRANSACTION_ID = '123456' AND a.TOTAL_AMOUNT >= 0.01 AND c.DP_DG_ID IN (1234) AND c.CUSTOMER_ID = a.CUSTOMER_ID[/i]So as you can see it is an Old Style join.I bring this up because when I change it to this :[i]SELECT SUM(QUANTITY) AS COUNTERFROM vw_TRANS_LINE_CATEGORY a JOIN vw_DLG_Participants c ON c.CUSTOMER_ID = a.CUSTOMER_IDWHERE QUANTITY > 0 AND a.IS_USEDUP != 1 AND a.TRANSACTION_ID = '123456' AND a.TOTAL_AMOUNT >= 0.01 AND c.DP_DG_ID IN ( 1234)[/i]and add a new Plan Guide it seems to only have high compilation times on the first couple of attempts then it has no compilation time, which is probably normal (I am using optimise for adhoc workloads).I have also noticed that in some cases the execution plan is the same between the two joins and sometimes it is different for the ANSI 92 JOIN.I think it might not be an old style join issue but an old style join issue between databases trying to use a Plan Guide?Any ideas??thanks

When DB restored to different server will database mapping and permissions stay?

Posted: 17 Feb 2013 09:32 AM PST

On Server A I have a database the is overwritten daily by a database on another server [Server B]After the first restore I map a group called "DOM\Reporting" to the database on Server A and assign read only permissions.The restored database comes from Server B, [u]this server does not contain an NT Group called "DOM\Reporting".[/u]So when I restore the database mapping has gone.I know when a SQL Account is used the accounts may need to be synced but I suppose I thought in the case using NT Authentication the security would sync between the Server Level acocunts and the database on restoring...it does not look like this is the case.So if I am correct would I either have to add the NTGroup to Server B with ReadOnly permissions so they gets transferred, and nothing extra required, or would I have to run a script to map the user to the restored database again and add the permissions -- every time the database is restored.Ideally I do not want to give the group "DOM\Reporting" any permissions to Server B.Hopefully I have explained it clear but please let me know if I have not.thanks for any help.

management Studio

Posted: 08 May 2013 07:28 AM PDT

Anyone knows if I only install SQL management studio, not the full version of an instance, can I use linked server to connect to Other SQL servers or Oracle servers? or through ODBC to connect to servers?Thanks,
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog