Author: Mohammad Sufian

Reading Mongod log in command prompt

Reading mongod log file in command prompt. Navigate to the log file location and type {TYPE log-file-name} type 1.log If the log file is quite big and you want to read the log file in batches use pipe operator with more tag type 1.log | more  

MongoDB : Check Replication Status

To check replication status, mongodb has rs.status() inbuilt function. abc:PRIMARY> rs.status function () { return db._adminCommand(“replSetGetStatus”); } rs.status ouput If secondary is synced and wasn’t created with slaveDelayoption then optime and optimeDate of secondary should be equal or close (if there are current operations) to those of primary. In that case stateStr should be equal […]

can’t start mongodb service

Stopped the mongod service manually and tried to start the service again which failed to start. Looked into the error log to see what caused the service to fail. Attached below is the error recorded in the error log, which was quite confusing and got me thinking how come permission where removed. [root@lx1 /]# cd /var/log/mongodb [root@lx1 mongodb]# […]

Encryption DMV’s

select * from sys.databases where is_master_key_encrypted_by_server=1 select * from sys.certificates select * from sys.dm_database_encryption_keys where encryption_state=3 SELECT * FROM sys.symmetric_keys SELECT * FROM sys.asymmetric_keys  

SQL Server Encryption with Asymmetric Keys Encrypt by Password

Continuation from http://sqlcontent.com/index.php/category/sql-server-encryption/ Asymmetric Keys Encrypt by Password  do not require to have master key in the database. The following example creates an asymmetric key named _rsa_customer_accmaster_key by using the RSA_2048 algorithm, and protects the private key with a password. USE RSASecData GO CREATE ASYMMETRIC KEY _rsa_customer_accmaster_key WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = ‘G%$D@3@f5-34+!DF’; GO […]

SQL Server Encryption with master and Asymmetric Keys

[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. […]

Enabling Temporal Table on In-Memory OLTP Table

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 […]

Introduction to SQL Server 2016 Temporal Tables

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 […]

deleting *.cache files from SQL Server

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 ;    

Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider SQLNCLI.1 is not registered — perhaps no 64-bit provider is available. Error code: 0x00000000. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”. End Error

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 […]

Can’t Expand MSDB in Integration Services Server

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 […]

TDE : Transparent Data Encryption

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 ( […]