Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

AWS RDS Sql Server READ_COMMITTED_SNAPSHOT

P
Pearlaqua last edited by

We're currently running Multi-AZ SQL SERVER (Mirroring) and would like to turn READ_COMMITTED_SNAPSHOT on. It doesn't look like this option is available in the parameter group or rdsadmin stored procedured, and executing ALTER DATABASE [database1] SET READ_COMMITTED_SNAPSHOTthrows an error that it is in a mirroring relationship. Any recommendations on how to accomplish this? We've tried restoring the database to test turning the partner since we're not sure how RDS responds, but all the restores are coming online as Always-On instead of mirroring so we haven't been able to test.
Reply Quote 0
1 Reply Last reply
emmalee last edited by

Here is a procedure from Microsoft on how to achieve what you are asking. Your issue is that the only session that can exist in the database when changing it to RCSI is the Alter DATABASE statement. However, the mirroring session exists, so there 2 sessions connected to the database. You only want a single session to exist.

You need to break mirroring , make the change to RCSI and then re-enable mirroring. Don't take my word for it. Review the Microsoft link below

https://techcommunity.microsoft.com/t5/datacat/how-to-enable-rcsi-for-a-database-with-database-mirroring/ba-p/305043
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

R

Linked Server to SQL Server 2000 server from SQL Server 2012
SQL, Database Testing • sql server sql server 2012 linked server sql server 2000 • • rosemadder

2

0
Votes

2
Posts

0
Views

Be careful when using SQLNCLI10 as described in connect item A workaround can be found here You have to first test the application that is using such SP's from 2012 to 2000. It also depends on where both server's are located and how much data you are pulling out using Linked Servers. Is there a reason that the other referenced databases cannot be moved to 2012 ?
SQL Error while connecting with SQL server Authentication
SQL, Database Testing • sql server sql server 2005 • • Marcee

2

0
Votes

2
Posts

0
Views

J

It looks like you have to enable sql authentication. Start the management studio and connect to your database. Right click porperties and in the security tab, allow sql authentication in addition. Then you can connect with the user sa. Atm it looks only on windows accounts.
C

SQL Server self recursive horizontal join
SQL, Database Testing • sql server pivot • • Cleofas

2

0
Votes

2
Posts

0
Views

K

I feel there are two approaches - left to right starting anywhere that has no parent, and right to left for anywhere that has a value. I'm going to go with the latter because it matches the rows to be produced. You should start by using a recursive CTE to turn your data into: RowVal, NodeVal, Value, Level 3, 3, 30, 0 3, 1, 30, 1 5, 5, 20, 0 5, 4, 20, 1 5, 2, 20, 2 5, 1, 20, 3 6, 6, 10, 0 There are plenty of examples for doing this... WITH theTree AS ( SELECT ID AS RowVal, ID AS NodeVal, Value, 0 AS Level, ID_Parent FROM theTable WHERE Value IS NOT NULL UNION ALL SELECT tr.RowVal, ta.ID AS NodeVal, tr.Value, Level + 1 AS Level, ta.ID_Parent FROM theTree AS tr JOIN theTable AS ta ON ta.ID = tr.ID_Parent ) SELECT RowVal, NodeVal, Value, Level FROM theTree ; Now you need to adjust the Levels, because we started at the leaves. MAX(Level) OVER (PARTITION BY RowVal) - Level AS NewLevel Now it's simple dynamic pivot stuff, which you can find very easily. Group by RowVal, Value. A column per NewLevel. And MAX(NodeVal) in each "cell".
Unable to ping/connect to azure sql server from sql server management studio
SQL, Database Testing • sql server ssms azure sql database • • emmalee

2

0
Votes

2
Posts

0
Views

The problem was with our company firewall. The port 1433 was blocked on the firewall. After removing the outgoing and incoming restrictions on firewall the SMSS started working.
J

GROUP BY in query with column by subquery
Software Programming • sql server sql • • jonetta

2

0
Votes

2
Posts

0
Views

D

Perhaps the use of CTE resolves and even leaves the code more readable.-- código #1 with CadaCarro as ( SELECT A.CARRO, (SELECT top (1) B.VALOR from tab2 as B where B.DATA <= A.DATA order by B.DATA desc) as VALOR from tab1 as A ) SELECT CARRO, sum(VALOR) as somaVALOR from CadaCarro group by CARRO; No http://portosql.wordpress.com/artigos/ There is an article on modular programming in T-SQL through the use of CTE.
R

READ_COMMITTED_SNAPSHOT support in SQL Server 2005 Express
SQL, Database Testing • sql server snapshot express edition • • rielyn

2

0
Votes

2
Posts

0
Views

P

Yes from that link it supports it. A Database Snapshot is a read only "frozen in time" (when the snapshot was taken) view of data. This is a tool useful in some recovery, reporting and rollback preparation scenarios. The isolation levels you are asking about are all covered by the line talking about Snapshot Isolation Level (Row-level Versioning). I would question what sort of workload you are putting against SQL Server Express that needs a snapshot isolation level as there are other restrictions on SQL Server Express that tend to make it not a great fit for busier, high concurrency apps (Most notably database size and memory restrictions) but this is certainly supported from that edition features matrix you linked.
Failed SQL Login - Source server same as destination cannot locate SPID SQL Server 2014
SQL, Database Testing • sql server sql server 2014 logins • • Marcee

2

0
Votes

2
Posts

0
Views

S

I've seen this before. I believe that it was due to autoclose. But I could not recall from memory. Credit here goes to Aaron Bertrand. Usually this means the login's default database is offline (perhaps due to AutoClose) or no longer exists. Resolve by fixing the missing database, or changing the login's default database using ALTER LOGIN (for older versions, use sp_defaultdb, which is now deprecated). This is reported as state 16 prior to SQL Server 2008. Error: 18456, Severity: 14, State: 40. Login failed for user ''. Reason: Failed to open the explicitly specified database. Blog post regarding SQL login errors
E

Error: Cannot Generate SSPI context
SQL, Database Testing • sql server authentication sql server 2008 connect errors • • elysha

2

0
Votes

2
Posts

0
Views

R

'Cannot generate SSPI context' is a generic error. It can be caused by many issues, like an outaded password, clock drift, Active Directory access permissions, failure to register an SPN and so on and so forth. There is no solution to this problem. The only 'solution' is to investigate the cause, as per KB811889 and/or Troubleshooting Kerberos Errors. Applying one solution or another from random Internet resources, w/o understanding the cause, may or may not solve the issue, may or may not cause frustration, may or may not cause irrevocable damage.
Transfer of tables from MSSQL to MYSQL
Software Programming • mysql sql server • • Analeea

2

0
Votes

2
Posts

0
Views

S

Turns out that OPENQUERY() Supports the alteration operations, and I was just able to make a request for a type.INSERT OPENQUERY (MYSQL, 'SELECT name FROM table') SELECT Name from Table To fill the data on the MYSQL beyond, through EXEC (QUERY) AT MYSQL Executing MSSQL requests on the MYSQL side, but the RPC needs to be included in the lining structures.
Transaction Log Backups Serial or Parallel?
SQL, Database Testing • sql server backup best prac languagetic lan ola hallengren • • Alberto

2

0
Votes

2
Posts

0
Views

J

Should I set up one job that uses ALL_DATABASES? or is it better to set up one job for each database and kick them all off in parallel? I would suggest to setup one job that would backup the transaction logs (serially). This would also make sure backup does not heavily utilities the I/O because you are running backup for database one at a time. What could be possible drawbacks with running in parallel Suppose you have 50 databases and you schedule transaction log backup of all the databases and they all start running in parallel this is definitely going to utilize lot of I/O. And if disk on which it is backing up files happen to have other data files you would see slowness. I have seen backup becoming slow when a poor query requesting lot of I/O runs along with backup job. Again suppose you have 50 database would it not be difficult to manage 50 jobs in SQL Server agent and what would be condition if you have 100-200 databases I would just not like it when you open the SQL Server agent and see lots of job, just keep it simple. I am sure the same case would be with you. The downside of serial would be that each successive backup waits until the other completes. This could potentially increase the amount of time between backups (ie, greater than 15 minutes). Transaction log backups are mostly small and if you have a busy database producing lot of log records you might need to change the backup frequency. Mostly I have seen transaction log backup completing fine when frequency is 15 mins. I don't think should be matter of concern for you. Plus my concern would be that a failure in one backup stops the others from happening, and I wouldn't want that to be the case I would say just don't worry about it. Transaction log backups just cannot fail unless you made some mistake. The mistakes can be The owner running the job is removed from AD Someone changed the recovery model of database. Insufficient disk space Apart from above I have not seen any reason for transaction log backup to fail. Its very robust you can rely on it.
G

What is Fastest way to shrink a datafile?
SQL, Database Testing • sql server sql server 2008 r2 migration shrink • • guilherme

2

0
Votes

2
Posts

0
Views

N

DBCC SHRINKDATABASE The fastest way I find to shrink a data file is to first use the SHRINK DATABASE command to see what it takes off the top. It will usually do this quickly--but not always. Keep an eye on blocking locks while you're doing this. caveat SHRINKDATABASE is impartial and will attempt indiscriminately attempt to shrink any file in the database. If you don't appreciate this behavior then don't run this one. DBCC SHRINKFILE After you've used SHRINKDATABASE you'll need to use SHRINKFILE. Here I find it's important to "chunk" the shrinks in small sizes. Also, I use a T-SQL DELAY to allow the database to gain back some resources to do other activities as needed. Using the script below you'll need to enter 1.> your database name, 2.> your logical file name of the file, 3.> and the upper and lower bounds, in MB, for your upper database size in MB and 4.> your lower database size in MB. You'll notice that it shrinks in 100 MB increments but, again, you'll need to find the size that's right for you. Warning: larger increments aren't necessarily more speedy. Don't worry "too much" if your lower bounds is too low--it won't/can't shrink past it. (You'll have to forgive my code, it's over 10 years old but still get's the job done). --Cut and paste the code that this T-SQL generates and run THAT code against your DB declare @next int, @last int, @DELAY VARCHAR(255), @SHRINK VARCHAR(1000) SELECT @DELAY='WAITFOR DELAY ''000:00:01''' select @next=102000, @last=50000 while(@next >=@last) begin --print @next SELECT @SHRINK ='use [<your DB name>] DBCC SHRINKFILE (N''<enter logical file name>'','+convert(varchar(255),@next)+ ')' print @shrink print @DELAY print 'go' select @next=@next-100 end
A

Migrating SQL Server 2000 to 2012, getting old versions of Views (edit: offender SQL Server 2008 R2)
SQL, Database Testing • sql server 2012 migration sql server 2000 • • amarantha

2

0
Votes

2
Posts

0
Views

A

So I finally found the error, and I'll just have to cross my fingers that this was a lone occurance. The view that kept reverting, after a sp_refreshview in SQL2008 r2, was named "qryCreateSnapshot". Then there was another view, a backup of sorts, named "qryCreateSnapshot_Old". The problem was that in the old view, the body was still "CREATE VIEW dbo.qryCreateSnapshot". I have no idea why this even compiled, but there you have it. So when I refreshed in 2008, I guess the new view got overwritten with the old. This did not happen in 2000. Now to compare hundreds of view names with their respective body name... For anyone in a smiliar situation, here is a small query to find mismatches between header name and body name of a view: SELECT O.name AS HeaderViewName, RTRIM(SUBSTRING(C.text, 17, LEN(O.name))) AS BodyViewName, CASE WHEN RTRIM(SUBSTRING(C.text, 17, LEN(O.name))) = O.name THEN 'Yes' ELSE 'Nope, fix body' END AS Match, C.text AS SourceText FROM sysobjects O INNER JOIN syscomments C ON O.id = C.id WHERE O.type = 'V' AND LEFT(C.text, 12) = 'CREATE VIEW '; I know it's not perfect, but it worked for my data and I found another ~15 mismatches.
SQL Server - Unable to Enlist Target to SQL Server Agent (MSX) after Collation Change
SQL, Database Testing • sql server sql server agent c languageentral manageme • • irl

2

0
Votes

2
Posts

0
Views

The resolution in my case was: Open appropriate Firewall Port for the SQL Server Agent. It resolved the Enlisting of Target to Master issue. After opening the port I was getting following error: The enlist operation failed (reason: SQLServerAgent Error: The EXECUTE permission was denied on the object 'sp_enlist_tsx', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229).) (.Net SqlClient Data Provider) This issue was resolved by dropping the Orphaned SQLAgent user in MSDB in CMS and then remapping the user back to MSDB and assigning it TargetServerRole Role.
Record in BD without mask applied by jQuery
Software Programming • c sharp sql server asp.net mvc • • Demir

2

0
Votes

2
Posts

0
Views

T

I see two options: (1) treat the value on the client side with JavaScript (2) treat the value on the server side with C#. For full warranty, you can implement both forms.JavaScriptWith JavaScript, you can assign a function to the event submit of the form and in this function remove all characters that are not digits of the CPF.$(document).ready(function() { $(".cpf").mask("999.999.999-99"); $(".cpf").addClass("form-control"); // vvvvv--- Lembre-se de colocar o seletor correto para o seu caso: $("#form").submit(function () { var cpfValue = $(".cpf").val(); // Remove os caracteres que não são dígitos: cpfValue = cpfValue.replace(/\D/g, ''); // Atualiza o valor no campo do formulário: $(".cpf").val( cpfValue ); }); }); In this way, the amount sent will be only numerical.C#This language is not my area, but a quick search saw that you can do something like:string cpfOnlyDigits = Regex.Replace(cpf, "[^\d]", ""); Then in the database, instead of saving the value of cpf, that would be the value received in the HTTP request, you would save the value of cpfOnlyDigits, being the filtered value, with only digits.
R

Replicating data between SQL Server 2014 to SQL Server 2005 - temporary solution
SQL, Database Testing • sql server replication sql server 2014 sql server 2005 • • Raziyah00

2

0
Votes

2
Posts

0
Views

E

Reading your question and thinking a bit more about a possible solution leads me to recommend transactional replication. Make sure you do implement a change freeze to your schema. From BOL : For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example, a SQL Server 2000 Publisher can have SQL Server 2008 Subscribers, and a SQL Server 2008 Publisher can have SQL Server 2000 Subscribers. Based on above truth, Take backup of your current sql server 2005 database and make sure you have instant file initialization enabled on all servers. Freeze all the data and schema changes Migrate your database from sql server 2005 to 2014 and do all post restore steps. Repoint your application to the new server instance. Set up T-Rep from sql server 2014 to sql server 2008R2 (this will be an intermediate server or instance and can be on the same new server if you want).You care about this step since, you want to be on supported version. Make sure you initialize replication manually using tsql. Now have another layer of replication from sql server 2008 R2 (intermediate) to your sql server 2005 server. Make sure you initialize replication manually using tsql. Note: If the servers are in same datacenter, then you can even do a snapshot which will be very fast. Above exercise will be worth, since you want a supported rollback plan. Since your business wants to keep things around for 2 weeks, you should be good once you test above. Note: I have not tried (as I have not been in a situation wherein I have to replicate data from sql server 2014 to 2005), but it should work (though not supported) theoretically. Give it a try for small database and you will easily be able to gauge the effort required.

AWS RDS Sql Server READ_COMMITTED_SNAPSHOT

Suggested Topics

Linked Server to SQL Server 2000 server from SQL Server 2012 SQL, Database Testing • sql server sql server 2012 linked server sql server 2000 • • rosemadder

SQL Error while connecting with SQL server Authentication SQL, Database Testing • sql server sql server 2005 • • Marcee

SQL Server self recursive horizontal join SQL, Database Testing • sql server pivot • • Cleofas

Unable to ping/connect to azure sql server from sql server management studio SQL, Database Testing • sql server ssms azure sql database • • emmalee

GROUP BY in query with column by subquery Software Programming • sql server sql • • jonetta

READ_COMMITTED_SNAPSHOT support in SQL Server 2005 Express SQL, Database Testing • sql server snapshot express edition • • rielyn

Failed SQL Login - Source server same as destination cannot locate SPID SQL Server 2014 SQL, Database Testing • sql server sql server 2014 logins • • Marcee

Error: Cannot Generate SSPI context SQL, Database Testing • sql server authentication sql server 2008 connect errors • • elysha

Transfer of tables from MSSQL to MYSQL Software Programming • mysql sql server • • Analeea

Transaction Log Backups Serial or Parallel? SQL, Database Testing • sql server backup best prac languagetic lan ola hallengren • • Alberto

What is Fastest way to shrink a datafile? SQL, Database Testing • sql server sql server 2008 r2 migration shrink • • guilherme

Migrating SQL Server 2000 to 2012, getting old versions of Views (edit: offender SQL Server 2008 R2) SQL, Database Testing • sql server 2012 migration sql server 2000 • • amarantha

SQL Server - Unable to Enlist Target to SQL Server Agent (MSX) after Collation Change SQL, Database Testing • sql server sql server agent c languageentral manageme • • irl

Record in BD without mask applied by jQuery Software Programming • c sharp sql server asp.net mvc • • Demir

Replicating data between SQL Server 2014 to SQL Server 2005 - temporary solution SQL, Database Testing • sql server replication sql server 2014 sql server 2005 • • Raziyah00

Linked Server to SQL Server 2000 server from SQL Server 2012
SQL, Database Testing • sql server sql server 2012 linked server sql server 2000 • • rosemadder

SQL Error while connecting with SQL server Authentication
SQL, Database Testing • sql server sql server 2005 • • Marcee

SQL Server self recursive horizontal join
SQL, Database Testing • sql server pivot • • Cleofas

Unable to ping/connect to azure sql server from sql server management studio
SQL, Database Testing • sql server ssms azure sql database • • emmalee

GROUP BY in query with column by subquery
Software Programming • sql server sql • • jonetta

READ_COMMITTED_SNAPSHOT support in SQL Server 2005 Express
SQL, Database Testing • sql server snapshot express edition • • rielyn

Failed SQL Login - Source server same as destination cannot locate SPID SQL Server 2014
SQL, Database Testing • sql server sql server 2014 logins • • Marcee

Error: Cannot Generate SSPI context
SQL, Database Testing • sql server authentication sql server 2008 connect errors • • elysha

Transfer of tables from MSSQL to MYSQL
Software Programming • mysql sql server • • Analeea

Transaction Log Backups Serial or Parallel?
SQL, Database Testing • sql server backup best prac languagetic lan ola hallengren • • Alberto

What is Fastest way to shrink a datafile?
SQL, Database Testing • sql server sql server 2008 r2 migration shrink • • guilherme

Migrating SQL Server 2000 to 2012, getting old versions of Views (edit: offender SQL Server 2008 R2)
SQL, Database Testing • sql server 2012 migration sql server 2000 • • amarantha

SQL Server - Unable to Enlist Target to SQL Server Agent (MSX) after Collation Change
SQL, Database Testing • sql server sql server agent c languageentral manageme • • irl

Record in BD without mask applied by jQuery
Software Programming • c sharp sql server asp.net mvc • • Demir

Replicating data between SQL Server 2014 to SQL Server 2005 - temporary solution
SQL, Database Testing • sql server replication sql server 2014 sql server 2005 • • Raziyah00