[BOL]:Public Key Cryptography (PKI) is a form of message secrecy in which a user creates a public key and a private key. The private key is kept secret, whereas the public key can be distributed to others. Although the keys are mathematically related, the private key cannot be easily derived by using the public key. […]
Problem : Log Writes bottleneck , Transaction waiting for WRITELOG which leads to I/O issues Solution : BEGIN TRAN DELETE FROM MyBigTable COMMIT WITH (DELAYED_DURABILITY=ON)
SELECT * FROM Sales.SpecialOfferProduct_inmem ALTER TABLE Sales.SpecialOfferProduct_inmem ADD StartTime DATETIME2 GO ALTER TABLE Sales.SpecialOfferProduct_inmem ADD EndTime DATETIME2 GO UPDATE Sales.SpecialOfferProduct_inmem SET StartTime = ‘19000101 00:00:00.0000000’, EndTime = ‘99991231 23:59:59.9999999’ GO ALTER TABLE Sales.SpecialOfferProduct_inmem ALTER COLUMN StartTime DATETIME2 NOT NULL GO ALTER TABLE Sales.SpecialOfferProduct_inmem ALTER COLUMN EndTime DATETIME2 NOT NULL GO ALTER TABLE Sales.SpecialOfferProduct_inmem ADD PERIOD […]
FROM : BOL Temporal table is released with SQL Server 2016.It was introduced first with ANSI SQL 2011 and is now supported in SQL Server 2016. *Do not mistake Temporal Tables with Temp Table.* Temporal table provides information about data stored in the table at any point in time rather than only the data that […]
Cache files are generated when Data collection is enabled and Collection set is running. You can delete the files if not required after disabling the Data Collector. USE msdb; GO EXEC dbo.sp_syscollector_disable_collector; To enabled Data Collector USE msdb; GO EXEC dbo.sp_syscollector_enable_collector ;
The requested OLE DB provider is not registered — perhaps no 64-bit provider is available. And what OLEDB provider you are using? Does it exist for 64-bit, is it installed? The error means that the OLDEDB provider you are using to connect to SQL server is only support on 32 bit run-times To resolve the issue […]
A recently built server with names instances fails to expand MSDB folder in Integration services. The Error which pops is very generic which can be seen in below image To get this working you need to update the MsDtsSrvr.ini file with correct sql instance name <ServerName> ServerName\SqlInstanceName</ServerName> MsDtsSrvr.ini file can be found at installation […]
DISABLE TRIGGER [Trigger Name] ON ALL SERVER GO DROP TRIGGER [Trigger Name] on all SERVER
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘P@ssw0rd’; go CREATE CERTIFICATE DB_TDE_Cert WITH SUBJECT = ‘DBTDE_Cert’; –Backup the key USE master; OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘P@ssw0rd’; BACKUP MASTER KEY TO FILE = ‘C:\data\EncryptionDemo\DBTDE_Cert.key’ ENCRYPTION BY PASSWORD =’P@ssw0rd’; GO BACKUP CERTIFICATE DB_TDE_Cert TO FILE = ‘C:\data\EncryptionDemo\DBTDE_Cert.bak’ WITH PRIVATE KEY ( […]
*This is NOT TDE* New feature in SQL Server 2016 : a new capability that protects data at rest and in motion. Always Encrypted encrypts data of individual columns end -to -end(protects data at rest and in motion). This feature currently support Ado.net & Sql Client of ADO.Net 4.6. Lets see how to configure this feature on New […]
The post is to demonstrate how to encrypt connection to SQL Server using SSL. At the bottom of the page you can see the video which demonstrates how to create a certificate and configure SQL Server to use SSL. SSL : Secure Sockets Layer is the most commonly used for web based client or native […]
LOGINPROPERTY function gives you the stored has password. SELECT LOGINPROPERTY(‘LoginName’,’PASSWORDHASH’); Output 0x020056986E23A5C403130F4B2DAD7ADBC835B9A662E70AE1937A7B7097DE2B36C58C3FDCB8912CF28DERP0A348918F559D53BF8868BA42D7FE1E400AE636862F939BC1DACFAEE3DB To use this hashed password , you have to use HASHED keyword. CREATE LOGIN LoginName WITH PASSWORD=0x020056986E23A5C403130F4B2DAD7ADBC835B9A662E70AE1937A7B7097DE2B36C58C3FDCB8912CF28DERP0A348918F559D53BF8868BA42D7FE1E400AE636862F939BC1DACFAEE3DB HASHED;
While configuring database mirroring we encountered below error message with GUI and in SQL Server Error Log GUI Error: Error : Database Mirroring login attempt by user ‘Domain\Login’ failed with error: ‘Connection handshake failed. The login ‘Domain\Login’ does not have CONNECT permission on the endpoint. State 84.’. [CLIENT: xx.xx.xx.xxx] Reason : Normally this error popup […]
DBCC FREEPROCCACHE (plan_handle) DBCC FREESYSTEMCACHE (‘ALL’) WITH MARK_IN_USE_FOR_REMOVAL;
In an Environment where you have 100’s of SQL instances and you need to test the connectivity to each of the instance or you need to deploy a script on each of the instance after verifying the connectivity to the instance.You need to have some sort of script which will connect to each instance and […]