Microsoft has released the most highly anticipated version of their Enterprise Database yet, SQL Server 2017. One of the significant features of this release is the introduction of SQL Server on Linux. Microsoft already confirmed a couple of years ago about this in the works, but, not all product see the light of the day. However, in this case, it did! SQL Server 2017 is out for almost a year now, and it might be a good idea to start this thread for both newbies and experienced professionals. You next possible employer could be asking SQL Server 2017 Interview Questions and your readiness around it.
SQL Server 2017 Interview Questions
Before we get started, to be clear, SQL Server 2017 is not a standalone product, but a combination of multiple products baked together. Since there is scope for entirely open-ended, we might skip some topics that you are looking for. So, with expectations being set, let us get started with SQL Server 2017 Interview Questions.
In this first section, we will focus on the high-availability questions. So, let’s get started.
What Does SQL Server Offer under Mission-critical availability?
- Reliability
- Integration
- Flexibility
- Efficiency
What are some of the Mission Critical Application Features?
- All-Linux infrastructure
- Application-level protection
- Automatic and within-seconds failover during unplanned outages
- No downtime during planned maintenance
- Performance-sensitive application
- DR required for regulatory compliance
What is Always On Failover Cluster Instances?
- Failover on SQL Server instance level
- Shared storage (SAN/SMB)
- Failover can take minutes based on load
- Multi-node clustering
- Passive secondary nodes
What is SQL Server Always On Availability Groups?
- Failover on database level
- Direct-attached storage
- Failover takes seconds
- Multiple secondaries
- Active secondaries
What are Basic Availability Groups?
- Availability groups with two replicas
- Replaces database mirroring
- Warm standbys for DR with Log shipping
What is offered in basic HADR?
- VM Failure
- Backup/Restore
- Resilience against guest and OS-level failures
- Minimum downtime for patching and upgrades
- Protection against accidental for malicious data corruption
- RTO in minutes to hours
What is offered in Mission Critical HADR
- Database-level protection
- RTO in seconds
- Recover from unplanned outage
- No downtime for planned maintenance
- Offload read/backup workload to active secondaries
- Failover to geographically distributed secondary site
How To Configuring failover clusters on Linux
- Install and configure SQL Server on each cluster node
- Configure shared storage and move database files
- Install and configure Pacemaker on each cluster node
- Install Windows
What would be the RTO and RPO of a highly available database?
The highly available solution will have 0 RTO and no data loss is acceptable
How to prevent VM level failure for a SQL server?
A: by placing high availability nodes in an availability set ensures that during either a planned or unplanned maintenance event, at least one virtual machine is available and meets the 99.95% Azure SLA.
Which is the fastest way to backup SQL Server databases to Azure?
A: You can use the file-snapshot backup to perform nearly instantaneous backups and incredibly quick restores.
Which of the following is not supported in Basic Availability Groups in SQL server 2017?
A: The secondary replica remains inactive unless there is a need to failover.
Which of the following is true concerning FCI in SQL Server
A: Failover happens at the instance level.
Is it possible to set up a cross-platform availability group spanning Windows and Linux?
A: Yes, Also, a cross-platform availability group can be used to migrate a database from SQL Server on Windows to Linux or vice versa with minimal downtime.
Which of these is only available with SQL 2017 and not with any previous versions?
A: Cluster less AG – read scale AG. In a read-scale availability group, there is no cluster
In Windows, failover clustering provides the cluster manager. What is the equivalent technology in Linux?
A: Pacemaker
Q: Which of the following capabilities are supported in an AlwaysOn Availability Group?
- Performing backup operations on secondary replicas
- Read-only access to one or more secondary replicas (readable secondary replicas)
SQL Server 2017 – New Features
What are the Industry-leading performance features in SQL Server 2017
- Improve transactional performance with a row-based in-memory analytics engine
- OLTP Speed analytics and reduce storage needs with ColumnStore compression
- Combine for real-time operational analytics (HTAP)
- Maintain performance when making app changes with Automatic Plan Correction
What is the compatibility level supported by SQL 2017
140
Is database mirroring deprecated in SQL Server 2017?
True
SQL Server 2016 supports Machine Learning?
False
On what platforms SQL Server 2017 is available on?
- Linux distributions including RedHat Enterprise Linux (RHEL), Ubuntu, and SUSE Enterprise Linux (SLES)
- Docker: Windows & Linux containers
- Windows Server / Windows 10
- Package-based installation
- Yum Install, Apt-Get, and Zypper
SQL Server 2017 runs natively on Docker?
TRUE
What are the tools provided in SQL Server 2017 for programmability
- SSMS
- SSDT
- Visual Studio Code mssql extension
- Native command-line tools—sqlcmd, bcp
SQL Server 2017: Security Questions
What are the fundamental changes with the GDPR?
- IT and training
- Controls and notifications
- Transparent policies
- Personal privacy
SQL Server provides enterprise-grade security capabilities on Windows and Linux, True/False?
True
Question: In SQL Server Row Level Security, The database system applies the access restrictions every time a tier attempts to access data. True/False?
True
Question: How to Grant read access to users on a required table?
“GRANT SELECT ON Sales.SalesOrderHeader TO Manager;
GRANT SELECT ON Sales.SalesOrderHeader TO SalesPerson280;”
Or
“GRANT SELECT ON Sales.SalesOrderHeader TO SalesPerson280;
GRANT SELECT ON Sales.SalesOrderHeader TO SalesPerson280;”
Answer: A
What does SQL Server 2017 Auditing offer?
- SQL Server Audit is the primary auditing tool in SQL Server
- Track and log server-level events in addition to individual database events
- SQL Server Audit uses Extended Events to help create and run audit-related events. SQL Server Audit includes several audit components
Question: Always Encrypted allows clients to encrypt sensitive data inside client applications? True/False?
Answer: True
Select ones which show Encryption at rest in SQL Server 2017?
- Transparent Data Encryption
- Backup Encryption
- Cell-Level Encryption
What are the “SQL Vulnerability Assessment benefits.”?
- Best practices for improved security
- Track permissions
- Identify and classify sensitive data