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
ADVERTISEMENT
Feb 18, 2015
How to check the delay in between replicas in AG?
View 3 Replies
View Related
Oct 31, 2014
is there a way to backup all stored procedures in a database?
View 4 Replies
View Related
Jun 25, 2015
What permission is required to run debug feature in SSMS(debug Stored Procedures). This is a development machine and developer requested for this.
EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'.
EXECUTE permission was denied on object 'sp_sql_debug', database 'master'.
Is there any option other than giving sysadmin privilege on SQL?
View 0 Replies
View Related
Jul 20, 2015
Replication-Replication Distribution Subsystem: agent PRODDATA6 failed. Could not find stored procedure 'sp_MSdel_dboActiveTask_msrepl_ccs'.
Replication-Replication Transaction-Log Reader Subsystem: agent PRODDATA6. The process could not execute 'sp_replcmds' on 'PRODDATA6'.
Replication-Replication Distribution Subsystem: agent PRODDATA6. Could not find stored procedure 'sp_MSupd_dboActiveTask'.
Looks like these sp's got deleted?
View 1 Replies
View Related
Feb 3, 2015
Is there any way to enforce table references in stored procedures? For Example, we have stored procedures with a ton of different formats, "dbo.table", "table", "db.dbo.table", etc. Can we make it so that for every stored procedure, the reference must be at least "dbo.table"?
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
Feb 27, 2015
I have two procedures Sp1 and Sp2. The Sp1 is calling from Sp2. If there come new parameters or removed from the first procedure Sp1 then it will effect the Sp2 and its execution will break if required changes not done. So I want that when any changes making in SP1 then i need alert about the Sp2 changes.
View 3 Replies
View Related
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
View Related
Sep 23, 2015
I was trying to create stored proc
[code="create procedure dbo.sp_table1
@idint
as
begin
updatetable1
setisarchived = 1,
[Code] ....
But the query was continuously blocking the query below
updatetable1
setisarchived = 1,
modtime = getdate()
whereid = @id
andisarchived = 0
I was not sure, why the create procedure statement is blocking the update statement.
View 1 Replies
View Related
Apr 30, 2015
In general as understand if we have a stored procedure that does operations like inserts or updates, it makes perfect sense to use a rollback operation within a transaction.
So, if something goes wrong and the transaction does not complete, all changes will be reverted and an error description will be thrown for example.
Nevertheless, does using a rollback within a try catch statement, make sense in a read only stored procedure, that practically executes some dynamic sql just to select data from some tables?
I have around 100 Stored procedures, all of them read only. Today a colleague suggested adding try-catch blocks with rollback to all of them. But since they are just selecting data, I don't see a clear benefit of doing so, compared to the hassle of changing such a big number of SP's.
View 9 Replies
View Related
Jul 2, 2014
I am looking for a table where Maintenance Clean Up Task configuration is stored. For example, Delete file older than the following - which is 2 days. Which table can I retrieve the setting in msdb ?
View 5 Replies
View Related
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
Oct 30, 2015
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
View 0 Replies
View Related
Apr 26, 2004
Can a user be granted the ability to create stored procedures with read only capabilities? I wouldn't mind be able to UPDATE but simply need to read only.
ddave
View 13 Replies
View Related
Apr 14, 2015
I inherited a lot of Servers to upgrade to 2014 to include an SSRS Server.
The encryption Key was never backed up and it seems that no one knows what the password is?
Do I have to manually load the reports? There are a lot of Reports.
[URL]
View 4 Replies
View Related
Sep 26, 2013
I want to use BCP to load data from a text file.
By default, constraints are turned off in bcp, so I use the CHECK_CONSTRAINTS hint.
bcp aborts if ANY of the rows contains a FK violation. No data get loaded.
So if I add the -b 1 batch size option, it loads all data UNTIL the first FK violation, but nothing after that.
I want to load EVERYTHING ... except for the violations. But bcp won't let me. Is there a way?
View 2 Replies
View Related
Jan 13, 2014
If I install an instance with Windows Only authentication, and then change it to Mixed Mode, if I enable the sa login, the password has already been set. What is the default? If it's generated, how secure is it? Is the password generated? What algorithm is used for that?
View 9 Replies
View Related
Mar 21, 2014
My sql databases in SQL Server 2014 has the status "suspend" as I saw in SQL Management Studio. I can't restore to serviceable condition sql databases through standard procedures. I need to restore .mdf file.
View 9 Replies
View Related
Jun 18, 2014
I am using a monitoring system where I can monitor a numeric SQL result assuming the result is one field and one row.I would like to do this to say monitor the free available space or percentage on say the Master database. DBCC SQLPERF gives me a few columns and results for all databases on the server.
View 2 Replies
View Related
Jun 25, 2014
In our environment applications are using a DNS name which points to the physical server ip address. Now we are planning to move to 2014. We are planning to have servers in different subnets so we will be having two ip adresses for listener. How we can point the DNS to the listener ips? If failover happens can the DNS point to the exact ip address of the listener where it's primary node?
View 1 Replies
View Related
Jul 31, 2014
Is there a way to schedule a sql job to run at different intervals
For eg:
The job should run at
7:00 Am
8:00 AM
and then at 10:00 Am
View 3 Replies
View Related
Nov 19, 2014
We installed the Sql 2014 in Test server.
We observed frequently
"Process 0:0:0 (0x1e10) Worker 0x00000006B6D341A0 appears to be non-yielding on Scheduler 13. Thread creation time: 12906028806348. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 13%. System Idle 84%. Interval: 70189 ms."
Is it better to run the profiler or performan counter?
What are the filters we have to select in the profiler to monitor the Sql server
View 0 Replies
View Related
Dec 8, 2014
I have a SQL server box running 2014 reporting services. I have another server running IIS v8.
I would like to be able to connect to the IIS site and be given the SSRS report browser.
So externally if I browse to [URL], I am presented with the report server interface, the same as if I browse to http://xxx.xxx.xxx.xxx/reports internally.
What are my options?
View 4 Replies
View Related
