SQL Server Admin 2014 :: Calculating Average Read / Write Latency Across Instance Using Counters

Apr 27, 2015

How you would calculate the average read/write latency experienced by a SQL Server instance during a specific time window in order to monitor this for multiple instances. From this MSDN blog, I know that you have to take multiple samples and do some calculations to get the correct latency.

[URL] ...

However, the SQLServer:Resource Pool Stats object tracks these numbers per resource pool and we want to get one number for the whole server. Since there can be a different base value for each resource pool, you can't simply sum the numerator values together. Here's some sample data from a server that illustrates the problem.

object_name counter_name instance_name cntr_value cntr_type
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) default 307318919 1073874176
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base default 25546724 1073939712
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) internal 2045730 1073874176
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base internal 208270 1073939712

I'm thinking I would need to do some sort of weighted average, but I'm not sure if that will result in the correct value. Here's the formula I am thinking about using currently before doing the calculation over time

((default * default[base]) + (internal * internal[base]))/(default[base] + internal[base])

Then to do the calculation over time, I'd use the changes in the calculated numerator and denominator to get the average.

Does this sound like to correct way to get this value? Is there a good way to verify?

View 2 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Can Have Secondary Replica As Read / Write Mode?

Oct 14, 2014

We have a reporting database which is refreshed daily from prod backup and later creating new tables/views/indexes as part of the refresh job. Is there a better approach we can implement in sql 2012/2014 for this scenario since we are planning to migrate to sql2014.

View 5 Replies View Related

SQL Server Admin 2014 :: Performance Counters Keep Disappearing

Feb 6, 2015

I have an Alwayson group with 2 Nodes. The SQL Performance counters are always showing on the secondary, but the primary is inconsistent.

I loaded the counters on the primary (unlodctr and lodctr ), restarted the SQL service, and confirmed that the counters were there. 2 days later the counters were gone again. I loaded them again. A week later we had maintenance, failed over to the secondary, and when we failed back to the primary - the counters were gone again….

View 0 Replies View Related

SQL Server Admin 2014 :: Cannot Connect To Named Instance (2nd Instance) From Local SSMS

Jul 22, 2015

I've two instances(Default, Named[dynamicsFINANCE]) running on SQL server 2014. However, when I try to connect to named instance say (dynamicsFINANCE) using SQL authentication from local SSMS, I get below error message:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

I assigned a static port number to the named instance [dynamicsFINANCE] 1450. I also setup the firewall rule to allow access to Port 1450.

View 5 Replies View Related

SQL Server Admin 2014 :: Get Average Of Two Largest Number Amount Three Column For Particular Identity

May 3, 2015

ID A B C AVG
------------------------
1 08 09 10 -
------------------------
2 10 25 26 -
------------------------
3 09 15 16 -
------------------------

I want to calculate the average of the larges two number from the column A,B & C for particular identity and store that average in the AVG column....

View 9 Replies View Related

SQL Server Admin 2014 :: DB Read Only Copy

Dec 11, 2014

What is the best approach for a read only copy of a database that is ~ 1TB. The primary database is fed nightly with an ETL process. We are currently trying to duplicate the ETL to read only server but that process is not going well. So we are looking at other options to let SQL make the copy.

The primary database is on a Win12R2 with SQL 12 or 14, a 2 node A/P failover cluster.

The read only copy will be on a Win12R2 with SQL 12 or 14. It is not a requirement to fail over to the read only copy if the primary should go down.

What would best the approach to accomplish the end result?

View 3 Replies View Related

SQL Server Admin 2014 :: Read Only On Secondary Replica

May 5, 2014

Assume if i have a connection(Application intent readonly) starts with reading, writing and again reading data for a report. how it will works in SQL 2014 Always availability on?

View 3 Replies View Related

SQL Server Admin 2014 :: Log Shipping With Read Only Mode?

May 6, 2014

we are about to start a logshipping with the secondary server in read only mode.

I just wanted to know the disadvantages of using a read only on the secondary server.

I mean whether the transactional logs will be restored periodically or it won't be restored till users are connected on the secondary server.

View 2 Replies View Related

SQL Server Admin 2014 :: Read Only Replicas And Stored Procedures

Aug 6, 2014

I am curious what other people have done to implement read-only routing for a large number of procedures.

Basically figuring out when to call procedures that are read-only with read-only intent.

We have a user application that passes an encrypted string to a web service that directs it to our SQL Servers.

I've been tasked with finding a way to make this happen without changing the application.

The only thing I have been able to come up with is writing something (which I did) that will identify whether something is read-only or not and storing a big list.

Then having the web service look up the given procedure and adding the intent where needed.

View 5 Replies View Related

SQL Server Admin 2014 :: AlwaysOn Read-Only Replica Routing

Jun 22, 2015

Having an annoying AG/AO problem with the read only routing side of it.

Let me give some specifics first:

2 SQL Server Instances, Not Clustered.
Availability Group is named 'Ireland'

There is a primary Replica and a Secondary Replica, named:

'IrelandPrimary' and 'IrelandSecondary'

There is a listener configured with the name 'ListenIreland' on Port 14330 (the two 3's are correct)

Read Only Routing URLS are configured as follows:
IrelandPrimary tcp://Ireland.dom.local:49891ALL
IrelandSecondary tcp://Ireland.dom.local:49841ALL

So now my problem:

When I try to connect using the ApplicationIntent=Readonly; or even using -K ReadONLY in sqlcmd I get the error telling me that my connection was actively refused.

This is connecting to the Listener, not the instance itself - that works fine. I'm at a bit of a loss now.

To explain what I am trying to achieve is a for a connection to be redirected to the secondary replica when its set for read-intent.

I've just noticed that it only fails when I specify ApplicationIntent=ReadOnly; If I omit the Intent It connects to the read-write database instead.

View 3 Replies View Related

SQL Server Admin 2014 :: Setting Up Aliased Instance Name

Jul 15, 2015

How to set up a specific aliased instance name. I have installed two identical named instances on a server called LONPOCSQL. The instance names are FINPROD and FINREP, so when connection to them remotely I connect to LONPOCSQLFINPROD or LONPOCSQLFINREP. My question is how can I configure it so that I can connect remotely to the instances in by referencing them as FINPRODFINPROD and FINREPFINREP?

The reason for this is that I need to replace a very old system where the applications connect to the sql servers by referencing them as FINPRODFINPROD and FINREPFINREP respectively. I'm trying to set up a 2-node SQL alwayson AG cluster for HA+DR with one server in the prod DC and the other in DR. I need both instances to live one the same box.

1. As far as I'm aware SQL Aliases only work when the connection is local, remote connections fail
2. I can't create a DNS alias called "FINPRODFINPROD" and point it at a listener name as slashes "" are not allowed in DNS CNAMEs
3. My old boss once built a SQL 2008 R2 failover cluster with two instances on it called "lonsql40lonsql40" but there is no documentation on how he did it and I can't find anything on the web

View 3 Replies View Related

SQL Server Admin 2014 :: Create Three AGs And Listeners In Same Instance

Jul 28, 2015

I have built a SQL 2014 Always on Cluster. I need to create three Availability groups and listeners in the same instance. Do I need 3 separate IP address for each listener or one IP address can be shared for all 3 Listeners ?

View 3 Replies View Related

SQL Server Admin 2014 :: System Objects Could Not Be Updated In Database X Because It Is Read-only

Jul 27, 2015

We installed SP1 for SQL Server 2014 this past weekend and got this error message in the logs. I found that if you set the db to read-write, it updates the system objects, even after SP1 has completed. Then you can set it back to read-only. I'm just posting this so other people can find it on the internet, as I wasn't able to find it specifically.

Error Log Entry:System objects could not be updated in database 'x' because it is read-only.

Problem: After installing SP1 for SQL Server 2014 you will find this message in the error logs saying read-only databases could not be updated.

Solution: Simply set the db to read-write and the system objects will get updated, long after SP1 was installed.

ALTER DATABASE [x] SET READ_WRITE WITH NO_WAIT

Then set it back to read-only:

ALTER DATABASE [x] SET READ_ONLY WITH NO_WAIT

You should then see these log entries:

System objects could not be updated in database 'x' because it is read-only.
Setting database option READ_WRITE to ON for database 'x'.
Starting up database 'x'.
CHECKDB for database 'x' finished without errors on 2015-07-25 01:02:28.143 (local time). This is an informational message only; no user action is required.
Synchronize Database 'x' (129) with Resource Database.
Setting database option READ_ONLY to ON for database 'x'.
Starting up database 'x'.
CHECKDB for database 'x' finished without errors on 2015-07-25 01:02:29.888 (local time). This is an informational message only; no user action is required.

View 0 Replies View Related

SQL Server Admin 2014 :: AlwaysOn Read Only Replica - SP Execution Error

Oct 8, 2015

We have always on setup in our environment with read only replica. The primary database has 2 schema one is a dbo and other xyz. We have some store procs created in dbo schema and xyz schema. These store procs are being used by SSRS reports to retrieve the data (select only) no data changes will be made.

when we run the store proc from the read only server the storeprocs in the dbo schema run fine but xyz schema are failing with the message saying failed to update the database as this is a read only...

View 1 Replies View Related

SQL Server Admin 2014 :: Write A Script That Will Basically Test Logins For Windows NT?

Jan 7, 2015

I am trying to write a sql script that will basically test logins for Windows NT Similar to when you bring up SQL Studio, and do run as windows NT I want to ensure my rights are not removed from SQL Servers and if so send my nice DBA an email.

How to do the connect as ? and check my permission is still set to access database with db_datawriter, db_datareader etc

View 2 Replies View Related

SQL Server Admin 2014 :: Setting Up AG On Default And Named Instance

Aug 26, 2014

Plan to setup AG on in both default and named instance of two nodes. Can we do it ?

View 9 Replies View Related

SQL Server Admin 2014 :: Ports Firewalls And A New Named Instance

Nov 12, 2014

I have an existing 2012 default SQL Express. It's set up on a VPS managed by a third party. I have an administrator account on this 2012 Windows server. I'm not much of a sysadmin or a DBA but I get around. ;)

I've installed a new NAMED instance on this VPS and can not connect to it with client tools (SSMS). If I remote in, I can connect this way.

What steps might a seasoned DBA expect to make when getting a new named instance ready for the world.

Assign a port? Check the port?

Open the firewall for the port?

Will this new named instance listen on a different port than the previously installed SQL Express instance?

View 6 Replies View Related

SQL Server Admin 2014 :: Does Each Node Need To Have Same Instance Name For Listener To Work?

Apr 15, 2015

Say I have 2014 AlwaysOn setup and working:

SQLNode1 ->Instance01
SQLNode2 ->Instance02

When I setup my listener: ListenerA...Do I need to use the instance name in it?

ListenerAInstance01 or ListenerAInstance02 depending on which SQLNode is the "active" availability group?

Am I better off to use the same instance name for both nodes, since my goal is to have all databases on both instances in the same availability group and sync'd? When SQLNode1 migrates over to SQLNode2 I will need to update the instance name in my connection string on the listener from ListenerAInstance01 to Instance02? When I connect with SSMS do I just use: ListenerAInstance01 (or 02)?

View 3 Replies View Related

SQL Server Admin 2014 :: Creating Named Instance On Cluster

Apr 22, 2015

I need to create a named instance on a sql cluster.

How do I perform this? Is it as simple as creating a named instance on a standalone machine? Is there anything I need to consider?

View 3 Replies View Related

SQL Server Admin 2014 :: Remote Access To Named Instance

Jul 6, 2015

I try to connect from a pc to a SQL Server on another pc. Both pc’s are in a workgroup. I want to connect from a Windows Forms application to a named instance on the other computer. By now I have been able to connect from one pc to SQL Server on the other with tcp:smurfin, 52782.

I want to be able to use servernameinstancename (instead of portnumber) to make a connection in a Windows Forms application.

I’ve checked / tried te following:

•In the properties of the instance, tab Connections, the option Allow Remote Connections is enabled
•In Configuration Manager: TCP is enabled
•The service SQL Server Browser is started
•On the tab IPAddresses, in the section IPAll, there is NO portnumber for TCP Port. And TCP Dynamic Ports has the nummer 52782
•I have created un inbound rule for port 52782 and also for 1434 (SQL Server Browser). And to be on the save side: a rule for 1433 as well.
•Restarted the service

If I run the following code in SQL Server, that same port number (52782) is returned:

EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
GO
SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID

[Code] ....

View 5 Replies View Related

SQL Server Admin 2014 :: AlwaysOn And Instance-level Items

Jul 16, 2015

How you are handling the replication of the many instance-level objects/items (logins, linked servers, server roles, database mail, operators, on and on) to the replicas in an AlwaysOn topology.

I'm especially curious about DBAs managing larger SQL Server environments. In my current environment, we have approximately 80 production SQL instances containing about 650 databases that require high availability and disaster recovery.

We use mirroring today and have a solid, home-grown solution for replicating the instance-level items from production to disaster recovery. AlwaysOn changes things a bit since we'll have multiple replicas and of course the database could be active on any one of those at any time. So my concern is about instance-level items being created in one instance but never deployed to the other instances participating in the AG group.

View 2 Replies View Related

SQL Server Admin 2014 :: Static Port Of Named Instance

Nov 3, 2015

I changed the Port of my named instance to use static port but still error log is giving two values ,why??

spid15sServer is listening on [ 'any' <ipv6> 50152].
spid15sServer is listening on [ 'any' <ipv4> 50152].
ServerServer is listening on [ ::1 <ipv6> 57518].
ServerServer is listening on [ 127.0.0.1 <ipv4> 57518].

View 5 Replies View Related

SQL Server Admin 2014 :: Read Only DNS Listener Failover Automation From Primary To Secondary

Mar 23, 2015

I have a 2 node cluster with 2 standalone 2k14 instances having alwayson setup. As per client requirement we have created a client access point with a cname alias in dns to connect to secondary replica. Now, everytime whenerver the roles switch over one has to manually move this resource from the previous secondary node to the new secondary node. This is tedious, and should not be done manually either, so I am looking for a way to automate it so that as soon as the role switches over, the resource group after some time should also switch over to the current secondary.

Env details
xa (current primary)
y2a (secondary)

client access point (resource group): aglreadtest

View 2 Replies View Related

SQL Server Admin 2014 :: How To Read And Load Extended Events Into A Table For Querying

Jun 19, 2015

I am setting up extended events more or less just fine, however I am a bit confused as to how to read and load them into a table for querying. In particular the offset part - is there a way to load just a given dates worth in?

I've got the files configured to be 20MB before rolling over, the XE is running all the time.

So if i load in the full file now, say that covers 2.5 days worth, when I load it again tomorrow to get the updated data I'm also reloading today, which is a waste?

I presume I am going about this wrong, but lack an example that really goes into detail of practicalities of loading this data.

View 0 Replies View Related

SQL Server Admin 2014 :: Grant User Read Access To Secondary Replica?

Sep 15, 2015

I have 3 servers taking part in an AlwaysOn AG.

I want to grant a user READ access to only one replica, and certainly not to the principle.

How do I go about doing this?

View 4 Replies View Related

SQL Server Admin 2014 :: List All Databases In Instance That Are Not Accessed Before Given Date

Jan 10, 2015

Looking for query that lists all databases, in an instance, that are not accessed before a given date (e.g., not accessed before December 31, 2014)?

View 6 Replies View Related

SQL Server Admin 2014 :: Leave DB In Restoring State After Attaching To Instance?

Apr 7, 2015

Why do I leave my DB in Restoring State after attaching to the instance? Could not this fire a redo activity on Transaction Log?

Or, How does sql server undo all uncommitted transactions on active vlf during recovery?

View 2 Replies View Related

SQL Server Admin 2014 :: Picking Static Port Number For Named Instance

Apr 3, 2015

Basically the question is, which number should I pick?

View 4 Replies View Related

SQL Server 2014 :: Does Running Multiple Queries In Same SP Cause Latency

May 14, 2014

I have the following:

(a) One Dynamic SQL Query that takes 37 ms when run as a single query or in an SP.

(b) Three SQL Indexed View queries that take 0 ms when run together.

When i add (a) + (b) in the same SP, i should get 37 ms + 0 ms = 37ms, but NO it takes 400 ms.

What is causing the extra 363 ms of latency.

View 9 Replies View Related

SQL Server 2014 :: Change Data Capture - Minimum Latency?

Jan 28, 2015

I am trying to use change data capture to load the data into the secondtable from table 1 which is coming from UI.

What will be the minimum latency??? Can we use incase of latency less than 5 seconds.

View 1 Replies View Related

Transact SQL :: Making Server Database Read / Write From Read Only

Jan 12, 2012

i attached adventure works in sql server 2008 and it showing as read only ,make it read write or remove read only tag from database.

View 11 Replies View Related

SQL Server Admin 2014 :: Does Security-admin Role Plus Deny Alter Any Login Cancel Each Other Out

Aug 27, 2015

I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.

I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.

I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).

So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.

View 0 Replies View Related

Recovery :: How To Manage Data Latency On Read-only AlwaysOn Databases

Aug 27, 2015

We have several read-only nodes in our AlwaysOn cluster, which are set to use Synchronous-commit mode, which ensures that the logs are updated on the read-only nodes before any update statements complete.  Even with this option, if we query a read-only node before the logs have been processed, we can read old data.  I would like to know a strategy to ensure that a read-only query will definitely return up to date information.  I had an idea that if I just used a different transaction type, like Serializable, that it might block the read-only query from actually getting the data until after the log file was processed, but I have not tried it, yet.

I would like to move more queries to the read-only nodes, in an effort to offload CPU utilization from the primary node.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved