SQL 2012 :: Read-only Routing In AG Group
Mar 16, 2015
Unable to access the 'T' database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation. Changed database context to 'T'.
I have 2 sql servers running in an AG group. SQL 2 is fully syncd and is used for read only reporting.The connection from the listener using ReadOnly works and connects to SQL 2. The problem is, when a database in SQL 2 is suspended from the AG group, the listener does not connect to the SQL 1 (primary).
The AG group is setup exactly like this:
Readable Secondary = Yes for both SQL 1 and 2, Availability Mode = Synchronous
why the listener does not connect to primary server?Connection in primary was set to "Allow read/write connections" for the AG. This meant "Connections where the Application Intent connection property is set to ReadOnly are not allowed. " Setting to "Allow All Connections" worked.
View 0 Replies
ADVERTISEMENT
Nov 15, 2014
I setup an AG with a listener (AO1) on three servers.
Server1 = Primary, Readable Secondary = No
Server2 = Secondary, Readable Secondary = Read-intent only
Server3 = Secondary, Readable Secondary = Read-intent only
Connecting to AO1 with default settings, I end up on Server1.
If I attempt to connect to AO1 with specifying ;ApplicationIntent=ReadOnly (with or without specifying the db name) in SSMS, still lands me on Server1.
Also on topic, in this scenario I would actually want Server1 to be a readable secondary (in the event of a fail-over). So If I did set it as Yes, even If I do get the issue above working, won't I always land on Server1 then?
View 9 Replies
View Related
Nov 30, 2015
My application supports a single database connection and in the app console I can produce reports. If I include the app database in an AlwaysOn availability group with a read-intent replica will SQL automatically route the “selects” to that second instance thus offloading my application’s reporting activities or I need a separate db connection (maybe from a reporting app or cli) with a connection specifying read-only intent?
View 4 Replies
View Related
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
Mar 24, 2015
How to identify whether the files are in read write or read only?
View 1 Replies
View Related
Jun 27, 2014
i have a database which get refreshed every day from client's data . and we need to pull heavy data from them every day as reports . so only selects happens on that database.
we do daily population of some table in some other databases from this daily refreshed DB.
will read uncommitted or NOLOCK with select queries to retrieve data faster.
there will be no dirty read as there are NO DML operation in that database so for SELECT which happens concurrently on these tables , will NOLOCK work?
View 2 Replies
View Related
Aug 15, 2014
Can a user of db owner role of a database change the databse option to read only and read-write?If not what permission I need to grant to the user?
View 1 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Feb 28, 2014
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year
Month
Date
And the following row groups:
Region
Product
SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
View 0 Replies
View Related
May 1, 2014
select top 15 count(*) as cnt, state from table
group by state
order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
View 9 Replies
View Related
Jul 2, 2014
I have an availability group with read only replicas, readable secondary set to yes and allow all incoming connections. I have also configured the read only routing (at least I'm pretty sure this is correct).
If I login to SSMS with a user in the sysadmin role I can view the objects in the read only replica database. If I login with a user in the public role I'm unable to get past the obvious error:
"The database databaseA is not accessbile. (ObjectExplorer)"
I've also tried adding the "ApplicationIntent=ReadOnly" option.
View 4 Replies
View Related
Aug 28, 2014
We are using 208r2. We used to generate simple reports against to prod. The production is running slow. Just we need the production fresh copy all the time. We are using mainly one database. So I need to have read only copy of that particular database which is sync with the production.
View 3 Replies
View Related
Dec 4, 2014
I have an inter-company problem.
The one company gathers data into a SQL database hosted within their secure network and now one of the companies (that they are contracted to gather the data for) wants a backup of the data or a replicated database to be able to be placed onto a external HDD or different SQL server on their own network and server.
The catch is that to maintain their data security the original company doesn't want the company that is receiving the data to be able to edit the data. They are concerned that someone might change the data and claim that the data that they gathered is incorrect.
Is there a way to ensure the SQL database remains entirely read only?
View 1 Replies
View Related
Jul 17, 2015
is there a way the data gets updated is through the replication only to subscriber ?
other users should only read the data , they shouldn't do any update at subscriber end.
View 0 Replies
View Related
Aug 24, 2015
we would like something like Mirroring with a Snapshot database so that the "replicated" site can read the database locally.Unfortunately, this requires SQL Enterprise edition on the reading site if I get this right.Is there a cheaper way to achieve this sort of functionality?
View 9 Replies
View Related
Oct 15, 1999
Can anyone HELP!!
I have a system set up with 2 servers, the master server replicating the data to the backup server. Each server has two network cards, 1 which links directly to the other server.
The problem is the replication is being routed round the network making the direct link useless.
How can this be ammended!!
They are both logging into the same primary controler.
I am open to sugestions
View 1 Replies
View Related
Mar 1, 2007
Is there a way to route a file directly to a printer? I have a process that creates PDF file in a known location and I would like to route them directly to the printer after creation.
View 2 Replies
View Related
Mar 12, 2008
Hi There
I cannot find anything online or in BOL about the proper format for a named instance in the routing address.
is it simply
ADDRESS = 'TCP://SERVERNAMEINSTANCENAME:4026'
or do you need to provide the port number for the instance.Not sure if TCP recognises the instance part?
Thanx
View 3 Replies
View Related
Jul 10, 2006
Hi
We are currently starting new project and evaluating possibility of using Service Broker in it but seems there could be some problems
We have a central server with a central database and several hundred of mobile? users. Information ( actually a subset which is specific to a particular user ) should be sent from/to central database and we wanted to use Service Broker for this.
When you set up a route in Service Broker you need to define Network address which I believe should be an IP address or DNS name.
The problem for us is that our mobile? users do not have static IP addresses and I am not sure we can assign a unique dns to all of them.
Mobile users can connect to internet in different places and hence get different IP addreses.
Is there a solution to this problem?
Rsgards,
Leonid.
View 2 Replies
View Related
Nov 21, 2013
I have the following code and trouble reading values of Bank Accounts. If i remove the line it says "xmlns="http://applications.apch1.com/webservice/schema/" then i my query is working. But i cant remove this becasue that is what i will get response from a web service. All the records are stored in the database with this line included.
DECLARE @MyXML XML
SET @MyXML = '<GetEmployeeDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<return xmlns="http://applications.apch1.com/webservice/schema/">
<CustomerID> A8339 </CustomerID>
<BankAccounts>
[Code] .....
View 2 Replies
View Related
Apr 15, 2014
We have poor performance spikes on a drive containing our log file but this is only for reads and seems to be at a time when we run a re-index job. If this is a likely correlation as to poor performance in reading the log file, and what reads are done from a log file.
View 2 Replies
View Related
May 6, 2014
I have an issue when connecting with application Intent=readonly when using Listener name. I can connect to the read only secondary with application Intent=readonly if I use the server name. It gives a host does not exist error when using listener. This happens only if I am using the application Intent keyword. I can connect for normal readwrite operation using listener name.
View 3 Replies
View Related
Jul 18, 2014
Considering trying to move 2008 acitve/passive cluster with log ship to day old read only 2008 server to 2012 active/passive to 2012 AG Read only server. Only problem is that read only instance may have to be a 2nd instance on a server. The new box is a beast 64 core 256 gig of RAM hp so this is no dog. So I have these choices
migrate 2008 active/passive cluster to 2012 active passive (this will be it's own ordeal)take new monster box and build two instances, one that will run the AG read only database, the other will house reporitng services and analysis services and a few dw databases. We are not heavy into deep dive analysis services yet kind of in it's infancy. Not sure if this other instance will be sql 2008R2 , may be able to do 2012. MY also have a few small sharepoint databases but they barely use it.
View 3 Replies
View Related
Aug 28, 2007
I've been reseaching on SSB and have read quite a number of posts on this forum that closely relate to what im trying to achieve. I have a solution im designing that ideally consists of a central server (SQLENTERPRISE) that will receive messages asynchronously from remote clients (SQLEXPRESS) spanning a wide geographical region over a GPRS virtual private network on a TCP/IP transport. This ideally is a star and spoke architecture and requirements dictate high level security, no loss of messages whatsover as well as high reliability and scalability.
To meet the security requirement in the context of the above scenario, i was thinking implementing both dialog and endpoint security using certificates would be ideal. I've downloaded some samples and have encountered problems simulating the above scenario on 3 machines (I Server & 2 Clients). Client1 sends messags successfully, the 2nd client doesnt possibly because the certificate on the server matches that to client1 since i ran that script first. How do i make it that the Server shares one certificate with all remote clients? Or is there a better way to configure SSB to work in this scenario?
Secondly i have hard coded the Server's IP Address in the Routes created on the Clients. Considering this is over a GPRS Virtual network, how can i make the clients dyamically "discover" the Server? The idea here is to make adding and setting up of new remote clients easy so that you can just plug them in to the existing network
Any help or pointers would be greatly appreciated.
PapaLee
View 1 Replies
View Related
Apr 25, 2007
Any ideas on why I get this message in the transmission status:
Classification has been delayed because the routing information is currently being updated.
I did create/drop some routes because I had pointed them to the wrong service, but that was some time ago.
I also just tried to restart the sql instance and it hung on stopping so I'm assuming something with the routing caused the issue.
Any ideas?
Thanks.
View 3 Replies
View Related
Dec 4, 2013
I have a table with one of the column of xml type. the column contains xml like given below. I want to read this xml from the table and show as below with T-sql query
"EmployeeID" "IndustryDome" "description "
Where Description value comes from the value of AllDome/ITEM/Dome /Description whose Dome equals to IndustryDome value
EX:
1166586 3951LX01 Description10
<GetEmployeeDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<return xmlns="http://applications.apch1.com/webservice/schema/">
<EmployeeID>1166586</EmployeeID>
<BankAccounts>
[Code] ....
View 2 Replies
View Related
Mar 13, 2014
Why I see absolutely no performance improvement when I spread my primary file group over 8 separate files on 8 separate disks, as opposed to having the primary file group all in one file on one disk.
I have set up 2 identical databases, one spread over 8 disks and one on one disk. Each database has a table called DATA and a column called VALUE. Value is NVARCHAR(200). I have filled each table up in both databases with 20,000 rows.
I then perform a select on each table in each database using CHECKPOINT and DBCC DROPCLEANBUFFERS to ensure I am reading from disk before each query and the execution times are identical in both databases.
I then ran the same queries against each database using a load testing tool and the batch requests per second on each DB is identical under load.
Surely the database with data spread over 8 disks should be FAR faster than the single file database as you have the combined reading power of 8 disks as opposed to 2??
Also, the same is happening for write speeds. When I create the data on both databases, the time it takes is identical on both.
BOL says it should be faster with multiple disks.
Just FYI this is on an Azure virtual machine and each disk is a locally redundant data disk that I have attached to the virtual machine.
Whether write speeds should increase with multiple disks or just read speeds?
View 8 Replies
View Related
Mar 28, 2014
What happens when an automatic failover occurs, in a two server AlwaysOn Availability Group configuration, where the secondary replica is configured as read-only?
Will it only allow read-only connections, or will it become read-write and can accept INSERT, UPDATES and DELETES when assigned the new role as Primary?
Is it correct that adding a third server/node, that just acts as passive and should be used for automatic failover, to support true HADR, would NOT need another license .. and that licenses would only be required for the previous Primary and Secondary (Read-Only) replicas?
View 3 Replies
View Related
Aug 10, 2015
I've created an excel spreadsheet with a data connection. This data connection uses a query that runs against a read-only database.
The issue I'm having is that the query never seems to finish running against the database, whether I open the Excel spreadsheet to view the data or run the query in SSMS.
I created the connection on the Data ribbon by going to From Other Sources --> From SQL Server and using the Data Connection Wizard.
Is there some kind of setting or property I'm missing that would allow this query to finish running?
View 6 Replies
View Related
Apr 8, 2006
www.codeplex.com/slm
Setting up Service Broker routes, endpoints and security is just too hard. One has to run pages and pages of Transact-SQL code just to get the Hello, world example work between two separate SQL Instances and the chances of making a mistake are overwhelming.
Well, not anymore! Ive just uploaded into the Service Broker team code gallery a new GUI tool for doing just that: easily configure two services to be able to have conversations. The tool uses the Service Listing concept. A Service Listing is like an identity card for a service. It is an XML document that contains all the necessary information needed to establish a conversation with that service. When two parties need to establish a conversation, they can exchange the Service Listings of the two services and the tool will create the entire infrastructure needed to establish the conversation. Optionally it can also create the message types and contracts supported by a target service in the initiator service database....
View 3 Replies
View Related
Aug 26, 2013
We are in the process of upgrading to a new SQL 2012 server but we have many packages that load data from dbf files created with FoxPro into one of our databases. We have not converted the packages and run them with DTS but we get the following error:
Error: 2013-08-26 11:05:27.36
Code: 0xC0209303
Source: BenchmarkLoad Connection manager "OLEDB NPIONE.Investment.middleTierSQL"
Description: The requested OLE DB provider SQLNCLI.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
[Code] .....
I searched for OLEDB and ODBC drivers for SQL 2012 64 bit but cannot find any that is newer than the 2005 that we have. What can I do short of changing the source files to overcome this issue?
View 8 Replies
View Related
Apr 14, 2014
I receive Error: 3967, Severity: 17, State: 1. Insufficient space in tempdb to hold row versions. We have 8 data files for temp db of 10210 GB size and given 10240 GB as max size.
As MS suggest to calculate the temp db file size and growth rate we need to monitor the perform counters Free Space in Tempdb (KB) and Version Store Size (KB) in the Transactions object.
basic formula: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction
My report disk utilizations says tempdb is full ? I thonk I need a shrink for the file .
Still I am confused in calculating the size , My perform counter gives me data as such
Free Space in tempdb (KB) 279938496
Version Generation rate (KB/s) 53681040
Version Cleanup rate (KB/s) 53422320
Version Store Size (KB) 258720
Version Store unit count 22
Version Store unit creation 774
Version Store unit truncation 752
View 4 Replies
View Related
Nov 11, 2014
I have an excel file which has dynamic columns
i.e. Col1, Col2, Col3 this week. next week i will have a new Col4 in the sheet. This will keep on adding every week.
My problem is to Unpivot the data
Date 8/2/2013 8/9/2013 8/16/2013
Stock 1,561 1,661 1,761
i.e. the abobe table should become as
Date Stock
8/2/2013 1561
8/9/2013 1661
8/16/2013 1,761
How can I unpivot the dynamic columns given that the columns will keep on increasing every week.
View 1 Replies
View Related
