The problem here is that your symmetric keys are not the same key, so it cannot be used in the destination database to decrypt the database on another database/instance/machine. Take a look at this link which describes the encryption hierarchy in SQL Server.
Basically, what is happening is you're creating a symmetric key on Source and using that to encrypt ...
If it is SQL Login and not Windows Login you might want to create the login (user) on the replica with the same SID, that will assure permissions are replicated as well.
CREATE LOGIN login_name WITH Password = 'strong_password', SID = 0xSome_value
You can get the SID from the primary instance by querying table sys.server_principals.
Select name, sid From ...
To add to the answer of Kin Shah, this query might help:
o.type_desc AS objtype,
u.name AS [schema],
o.name AS object,
i.type_desc AS idxtype,
i.index_id AS idxid,
ISNULL(i.name, '*Heap') AS [index],
p.partition_number AS partitn,
Had same issue and found following info related to applied SP/CU:
FIX: Cannot reclaim unused space by using shrink operation in the table that contains a LOB column in SQL Server
I leave it here in case it helps someone!
First, check what is your recovery mode, having log errors like the ones you pasted are often related to recovery mode Full.
Check it using following query:
select name, recovery_model_desc from sys.databases
If it is anything but simple, every bit of data is saved multiple times, first in data file and in the log file. If you have staging tables, all ...
The error messages are indicating that the log file is full. SQL Server has two files (at least) for every database; one is the data file and one is the log file. On your particular server, it is possible that the log file is on a disk that doesn't have enough space for the log file while the data file may be on a disk where it has plenty of space.
If the ...
A late review for an interview:
SELECT TOP 1 Count(InvoiceID) Most , VendorState
FROM Vendors V JOIN Invoices I
GROUP BY VendorState
ORDER BY Most DESC
It ran with the right answer. Thanks all for your input.
A similar one: The state with the most vendors:
SELECT TOP 1 Count(VendorState) MostV , VendorState
FROM Vendors V JOIN ...
Log truncation frees space in the log file for reuse by the transaction log. You must regularly truncate your transaction log to keep it from filling the allotted space. Several factors can delay log truncation. One of the most probable reason - you didn't make log backups. You need to backup your transaction log and after that you will be able to shrink it. ...
Have you checked log_reuse_wait_desc from sys.databases, and what it is shows?
You probably got to take appropriate action based on the result:
select name, log_reuse_wait_desc, recovery_model_desc
Also, if the database set to FULL RECOVERY MODEL, you need to increase frequency of LOG Backups depending on Log generation, there is no ...
Well...the AD part is unnecessary, that only gives authentication/login privileges to users (basically all AD does in SQL Server is tell SQL Server "I have verified that this person is who she/he says it is and you can allow this person to connect because I say so" - this is a very basic explanation, of course).
If you want to set up privileges about what ...
You can use SET IDENTITY_INSERT my_table ON before performing the insertion of the data in you local table and on finish use SET IDENTITY_INSERT my_table OFF and your column will take the following value.
SET IDENTITY_INSERT [dbo].[table] ON
INSERT INTO [dbo].[table] ([CustomerID] ,[FirstName]) VALUES (1 ,'qqq');
INSERT INTO [dbo].[table] ([...
I had lots of confusion around the topic - In AG cluster restoring Log backup following with Copy - only full backup.
It is now working fine. We can use copy only backup from secondary replica:
RESTORE DATABASE [xxxxx_testDB] FROM
DISK = N'D:\Backups\FULL_COPY_ONLY\xxxxx_testDB_FULL_COPY_ONLY.bak'
WITH FILE = 1,
MOVE N'xxxxx_testDB' TO N'...
For integer to date conversion you may consider using the system function in msdb called dbo.agent_datetime(date int, time int). This will automatically convert to a datetime (which you could then cast as a date).
Sounds like DOMAIN\GroupName is a group and DOMAIN\user.name is a member of that group. This means that DOMAIN\user.name has access to SQL Server through that group. You have 2 options:
Create another group, let's say DOMAIN\NewGroupName and grant that group INSERT and UPDATE, then make DOMAIN\user.name a member of that group.
This is not the fastest ...
how to set the value of @MaxItemSeqNum to the returned result
SET @MaxItemSeqNum = ( SELECT MAX([Table1].[Item_Seq_number])
WHERE Batch_Id = 1
Group By Batch_Id );
SELECT @MaxItemSeqNum = MAX([Table1].[Item_Seq_number])