Thanks Beatrix,

Can developers use "Secondary instance name" from management studio so that they can connect to Secondary replica databases?

This is how our AAG configured:

Having this configuration, how do I restrict developers running select queries to go to secondary replica ?

SELECT UnitId, ProductID, Occurences = COUNT(*)
FROM dbo.Product

GROUP BY UnitId, ProductID WITH ROLLUP
having count(*)>1

Results:

UnitId ProductID Occurences
NULL NULL           2208020
NULL NULL           2208020

I...

• This reply was modified 5 years, 1 month ago by  jdc.

Hi Jeff,

Below is the index that key lookup is using. Can you please let me know how I can use COUNT with a GROUP BY that matches the keys ?...

I have a query which shows the differences but it is not giving if the column datatype changes. How do I get that?

select isnull(db1.table_name, db2.table_name) as ,
isnull(db1.column_name,...

• This reply was modified 5 years, 1 month ago by  jdc.

found it.

delete from table output deleted.*

thanks bmg002. I really appreciate your help.

And also I want to do a schema comparison for the source database and Archive database tables ?

I want to perform a check before...

• This reply was modified 5 years, 1 month ago by  jdc.

Thanks so much!

Can you please advise me how exactly I should add @@rowcount to row count for inserts into each tables also deletes from each table?

I tired to have below...

• This reply was modified 5 years, 2 months ago by  jdc.
• This reply was modified 5 years, 2 months ago by  jdc.

Thank you.

Yes, Transaction table has duplicate values for OperatorID but they are unique with TransactionID. Please see the screenshot.

I have updated the insert logic in archive.dbo.operator table as you advised....

• This reply was modified 5 years, 2 months ago by  jdc.

Thanks Steve,

Here is the DDL for the tables:

Transaction:

CREATE TABLE [dbo].Transaction(

[TransactionID] [int] NOT NULL,

[OperatorID] [nchar](32) NOT NULL,

[CustomerID] [char](100) NULL,

[TransactionNumber] [int] NOT NULL,

[TransactionDateTime] [datetime] NOT NULL,

[CreateDateTime] [datetime] NOT NULL,

PRIMARY KEY CLUSTERED

(

[TransactionID] ASC

)WITH...

I found that the issue why I'm getting primary key violation while inserting to archive database.

Here the query failing in the stored procedure:

insert into archive.dbo.operator
select * from...

• This reply was modified 5 years, 2 months ago by  Steve Jones - SSC Editor. Reason: format code

Thanks bmg002,

I will try SSIS for data archiving and see if that helps.

I have used below procedure and it is working fine but unable to create keys and Indexes.

declare @tablename varchar(500)
declare @sql varchar(5000)
declare @idname varchar(50)
declare @tablearchive varchar(500)

--Select all the...

• This reply was modified 5 years, 2 months ago by  Steve Jones - SSC Editor.

I use datapump, expdp and impdp in Oracle to refresh lower environments. Do we have similar functionality in SQL Sever instead of backup/restore ?

• This reply was modified 5 years, 3 months ago by  jdc.

Thanks everyone for your valuable inputs.