Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

Posted on Updated on

by Stacia Varga (Author), Denny Cherry (Author), Joseph D’Antoni

With Microsoft SQL Server 2016, a variety of new features and enhancements to the data platform deliver breakthrough performance, advanced security, and richer, integrated reporting and analytics capabilities. In this ebook, we introduce new security features: Always Encrypted, Row-Level Security, and dynamic data masking; discuss enhancements that enable you to better manage performance and storage: TemDB configuration, query store, and Stretch Database; review several improvements to Reporting Services; and also describe AlwaysOn Availability Groups, tabular enhancements, and R integration.


Posted on Updated on

If you have taken backup from one server and are going to restore on another server, but the problem is on New server the same directory structure or even the drive is not available as of the one from where the backup was taken.. In that case you will get error on restoring as SQL Server tries to create files on same locations.

To get rid of this situation use WITH MOVE command with your RESTORE as shown below:

RESTORE Database [NewWorkDB]
FROM DISK = ‘E:\WorkDB.bak’
WITH MOVE ‘Test’ to ‘E:\NewWorkDB.mdf’, –Test is logical name of datafile
MOVE ‘Test_log’ to ‘E:\NewWorkDB_log.ldf’ –Test is logcal name of log file

To get list of files being restored prior to perform a RESTORE you can use FILELISTONLY command.

RESTORE FILELISTONLY – How many Files are in backup set?

Posted on Updated on

Sometimes prior to restoring a backup we need to see how many files will be created if we restore a backup and on what location those files will be created. For that we can use RESTORE FILELISTONLY command to get list of files.

RESTORE FILELISTONLY FROM DISK=N'e:\location-of-backupset';

The above command does NOT restore the backup but will just only list the info about files that where SQL Server is going to create the data and log files and of how much size, their logical and physical names.

If “PhysicalName” location is not available in your restoring-server you can use WITH MOVE command to explicitly tell SQL Server that where these files should be created.


Posted on Updated on

DBCC INPUTBUFFER (spid) shows the last statement executed for a given SPID. This statement is mostly used for troubleshooting purposes to determine the exact command a particular SPID is running or have executed. To run this command you must be a member of the sys admin fixed server role, or have VIEW SERVER STATE permission (if SQL 2005+).

To get the SPID you can use sp_who2 to find out which SPID is taking a lot of resources.

To execute, simply replace the SPID below with the one you want to spy on.

Difference Between sp_who & sp_who2

Posted on Updated on

Purpose of sp_who and sp_who2  is same.But some key differences are:

sp_who supports the limited columns information about currently running process in the SQL Server.

sp_who2 supports some extra columns information about currently running process in the SQL Server then sp_who command.

sp_who command is documented and officially supported by the Microsoft.

sp_who2 command is undocumented and don’t have any support from Microsoft.

sp_who2 is more widely used for better information of the running process.

The following images show the clear information about  sp_who and sp_who2.



SQL Server DBA Exam 70-764

Posted on Updated on

  • Technology: SQL Server 2016/2017/2019
  • Credit toward certification: MCSA
  • Microsoft Site Link: Exam 70-764

Configure data access and auditing (20–25%)

  • Configure encryption
    • Implement cell-level encryption
    • Implement Always Encrypted
    • Implement backup encryption
    • Configure transparent data encryption
    • Configure encryption for connections
    • Troubleshoot encryption errors
  • Configure data access and permissions
    • Manage database object permissions
    • Create and maintain users
    • Create and maintain custom roles
    • Configure user options for Azure SQL Database
    • Configure row-level security
    • Configure dynamic data masking
  • Configure auditing
    • Configure an audit on SQL Server
    • Query the SQL Server audit log
    • Manage a SQL Server audit
    • Configure an Azure SQL Database audit
    • Analyze audit logs and reports from Azure SQL Database

Manage backup and restore of databases (20–25%)

  • Develop a backup strategy
    • Back up very large databases
    • Configure alerting for failed backups
    • Back up databases to Azure
    • Manage transaction log backups,
    • Configure database recovery models
    • Configure backup automation
  • Restore databases
    • Perform piecemeal restores
    • Perform page recovery
    • Perform point-in-time recovery
    • Restore file groups
    • Develop a plan to automate and test restores
  • Manage database integrity
    • Implement database consistency checks
    • Identify database corruption
    • Recover from database corruption

Manage and monitor SQL Server instances (35–40%)

  • Monitor database activity
    • Monitor current sessions
    • Identify sessions that cause blocking activity
    • Identify sessions that consume tempdb resources
    • Configure the data collector
  • Monitor queries
    • Manage the Query Store
    • Configure Extended Events and trace events
    • Identify problematic execution plans
    • Troubleshoot server health using Extended Events
  • Manage indexes
    • Identify and repair index fragmentation
    • Identify and create missing indexes
    • Identify and drop underutilized indexes
    • Manage existing columnstore indexes
  • Manage statistics
    • Identify and correct outdated statistics
    • Implement Auto Update Statistics
    • Implement statistics for large tables
  • Monitor SQL Server instances
    • Create and manage operators,
    • Create and manage SQL Agent alerts
    • Define custom alert actions
    • Define failure actions
    • Configure database mail
    • Configure Policy-Based Management
    • Identify available space on data volumes
    • Identify the cause of performance degradation

Manage high availability and disaster recovery (20–25%)

  • Implement log shipping
    • Configure log shipping
    • Monitor log shipping
  • Implement AlwaysOn Availability Groups
    • Configure Windows clustering
    • Create an availability group
    • Configure read-only routing
    • Manage failover
    • Create distributed availability groups
  • Implement Failover Cluster Instances
    • Manage shared disks
    • Configure cluster shared volumes

Truffle: TypeError: Data location must be “memory” for return parameter in function, but none was given.

Posted on Updated on

There are some updates in solidity 0.05.0 that Explicit data location for all variables of struct, array or mapping types is now mandatory. This is also applied to function parameters and return variables. So the changes are as follows:

pragma solidity^0.5.0;

contract Contract {
    string public name;

    function Contracts(string passedName) public {
        name = passedName;

    function setName(string newName) public {
        name = newName;



To This:

pragma solidity^0.5.0;

contract Contract {
    string public name;

    function Contracts(string memory passedName) public {
        name = passedName;

    function setName(string memory newName) public {
        name = newName;



C++ sizeof Operator

Posted on Updated on

sizeof is a keyword and a compile-time operator that determines the size of given variable OR data-type, and returns result in bytes.

We can also use sizeof keyword to get size of classes, structures, unions and any other user defined data type.


sizeof ( data type)

to get the size of given datatype. like: sizeof(int)


sizeof (variable name)

Example program:

#include <iostream>
using namespace std;

int main() {
cout << "Size of char : " << sizeof(char) << endl;
cout << "Size of int : " << sizeof(int) << endl;
cout << "Size of short int : " << sizeof(short int) << endl;
cout << "Size of long int : " << sizeof(long int) << endl;
cout << "Size of float : " << sizeof(float) << endl;
cout << "Size of double : " << sizeof(double) << endl;
cout << "Size of wchar_t : " << sizeof(wchar_t) << endl;

return 0;

Blockchain from Bitcoin’s perspective

Posted on Updated on

Blockchain technology is used as basis for a secure and transparent distributed ledger for the Bitcoin cryptocurrency. Its decentralized, public and immutable properties solve the double spending problem and allow every participant of the network to read the transaction history, help in
the validation process and pay and receive Bitcoin.

Cryptographically complex math ensures that everyone can do transactions with everyone without the need for a trusted third party. Next to financial transactions, this also holds for other claims. Entities can put claims on a decentralized ledger by digitally signing it, which allows any other entity to verify that these claims are made by that specific entity.

What is CURL ?

Posted on Updated on

cURL is basically a Client URL Library or a Command line tool, can be considered as a non-interactive web browser which is used to move data to or from server which supports various protocols such as:

cURL allows users to perform multiple files download / uploads with a single command. Multiple URLs can be specified in single command and they will be fetched in given order. It also supports proxy details, file uploads via FTP, sending requests over HTTP protocol, establishing connections via SSL, getting and setting cookies, user and password authentication, SMTP authentication and sending emails, authentication via POP3 and IMAP and many more. Simply, cURL can be used for everything that is related to data transfer using Internet protocols.