The only difference between full and full-copy is that full-copy does not break the differential chain. A copy-only backup doesn’t affect that differential base. Neither Full or Full-Copy only backup breaks the log chain as neither of them truncates the log file.

SIMPLE, FULL & BULK LOGGED

Atomicity, Consistency, Isolation, and Durability

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT and  SERIALIZABLE

It updates the distribution of key values for an index, Table or an indexed view. Statistics can be for columns or indexes.

Trace flags :1204 & 1222

DBCC TRACEON(1204)

“Deadlock graph”, “Lock:Deadlock” and “Lock:Deadlock Chain” events

In case of deadlock, SQL Server will pick one task/session, whichever will be least expensive to rollback and kill it. This session/task is termed as a deadlock victim.

Master, Model, TempDB & MSDB.

MSDB

It s a read-only database and has all system tables and views. These objects appear in the sys schema of each database.

It’s a template database, changes made to Model database are applied to all databases created afterward.

Sys.objects , sys.allocation_units  , sys.columns etc

Sysadmin, server admin , setupadmin

db_owner, db_securityadmin and db_accessadmin

Example:  GRANT Execute on TestTable to TestUser1

REVOKE

Always Encrypted, Dynamic Data Masking, Stretch database, Polybase etc

No, if the primary database is not online, Secondary database should be brought online manually.

Log shipping is at database level while replication replicates data at the individual table level.

high-safety mode and high-performance mode

No, it is an optional instance. It is needed only if automatic failover is required.

Yes, the database needs to be in FULL recovery mode.

Active – Active and Active – Passive configuration type

• SQL Server Database Emgine
• SQL Server Agent
• SQL Server analysis services

Heap

Only one

sys.dm_db_index_physical_stats

Generally, when the fragmentation level is more than 30% , it is advisable to rebuild indexes and when the fragmentation is less than 30% we can re-organize indexes.

• DBCC CHECKDB
• DBCC Show_statistics
• DBCC Opentran
• DBCC Input buffer

1433

• Records which have been marked for deletion (logically deleted) but have not been physically deleted are termed as Ghost records. This feature was introduced to enhance performance.
• SQL Server has a background thread that checks for Ghost records on a periodic basis and deletes it .

• It should have atleast two files – Data file and Log file.

Yes, Indexes can be moved to a separate data file. This will help from performance point of  view as well.

• Logins are at instance level while Users are at the database level. Instance level privileges and permissions are granted to Logins and they are also known as ‘Server principals’.
• Users are at the database level and are granted database level permissions. They are also known as
• ‘database principals’. Logins are mapped to users at the database level.

• A database user (based on a login) which is not mapped to a valid login is termed as an Orphan user.
• This can occur when the login is deleted or the database is moved from one server to the other.

A login can change its own password, but it needs at least “Alter Any Login” permission to be able to change the password for other logins, except for logins with “sysadmin” role.

Whenever SQL Server starts, it needs a minimum of below listed three startup

parameters:

• Master database data file location (-d parameter)
• Errorlog file location (-e parameter)
• Master database transaction log file location (-l parameter)

PAGEIOLATCH_SH, CXPACKET, SOS_SCHEDULER_YIELD, ASYNC_NETWORK_IO etc

This can be done using the Database Mail feature

Run the following query: SELECT * FROM sys.configurations WHERE name = ‘Database Mail XPs’GO

Enterprise, Standard, Express and Developer

Enterprise

We cannot use production data in Developer edition.

Always Encrypted technology, row-level security, dynamic data masking, transparent data encryption (TDE) etc

SQL Server 2017

Backup, Copy , Restore jobs

Both MySQL Server and MS SQL Server are used to maintain big comoanies database system but there are differences between them.MySQL is an open source Real time Database Management System and MS SQL Server is the non-free licensed computer software. MySQL is used for database management and MS SQL server is only used to manage data.

Model Database maintains information related to system-level for SQL server like login details, linked servers, endpoints, and also other details of the configuration setting. The model database is also a store point for SQL Server for storing other database information and their file location.

SQL profiler is a utility which you can use for looking the traffic in SQL Server and control the traffic on a particular instance.

3 types of replications.

• Snap: It captures snap short of instance
• Merge: Merge uses the snap replication and perform central repository serve.
• Dealing: Dealing works both with snap and merge .

SQL Agent give a scope to the database administrator to execute the process automatically, managing other system based works and a backup process.

One of the important data recovery tool in SQL server is Checkpoint and it maintains the change and modification in cache of database page.

Database Console Commands in short DBCC is available in 4 types.

• Maintenance
• Miscellaneous
• Validation
• Informational

You can set the fill factor on your indexes, at the time of re-indexing SQL server will automatically get the update about how much free space to leave in the index pages.

It is used to give power to the computation of question optimization statistics table or indexed read. Your data changes and sometimes auto-update statics are not enough; in that case, a query is a beneficiary for updating those kinds of statistics to more frequently.

Correlated sub-query is a subquery that uses values from outer side query and as it is related with the column of outer side query you must re-execute it for each row of the result.

Windows Authentication and mixed-mode. You can use both Windows Authentication and SQL Server Authentication in the mixed-mode to log into your SQL server.

It is very common Questions in most of the interview. By asking this question the selection board wants to know about your working experience with multiple versions of SQL Server and make an idea about your capability to choose the right person for the job.

2000,2005, 2008 and SQL server7 are the published version till date. The work experience depends on every individual candidate.

Two types of Indexes, Clustered Indexes and Non-Clustered Indexes.

Clustered index is used in table formation, to arranged data pages according to its key. In Non-Clustered index, the leaf level pages do not contain data pages instead it contains pointers to the data pages..

Extra added features in SQL Server 2005 are

• Database Snapshots
• Dynamic Management Views
• Resource Database
• SQL Server Integration Services
• Database Partitioning
• System Catalogue Views
• Support for Analysis Services on a Failover Cluster.

The solutions are Log Shipping, Database Mirroring, Failover cluster and Replication high-available in SQL Server.

Select the unsuccessful job, right click and select view history from the drop-down menu. The implementation history of the job is displayed and you will select the implementation time. There you will get all the details about the error and how much time to fix it .

1433

• The contained information will be a most of 32 to 767 files.
• 2 types of data files, Primary data file and Secondary data file(s)
• There is only one Primary file and multiple numbers of  secondary information files which is smaller amount than 32 to 767 files.

Data Control Language.

RANT, DENY and REVOKE.

Fill issue is a setting used to apply Index in SQL Server. The fill issue determines price of the proportion of written information in a created/rebuilt index page.

It is zero by default.

You can get it from Management Studio, right-click the SQL Server and choose properties, change the value as you wanted there and click OK to save the changes.

In the Installation procedure of SQL Server, the SQL server installed a database called System Database. There are 4 types of system database master, MSDB, model, and TempDB. Most of the SQL server systems are maintained by system databases like logins, databases, linked servers, jobs, schedules, reports, report data sources etc. If the system database corrupts the whole SQL server will stop. The user database is porously created to store information for our necessity. If the user database corrupts it will not affect the SQL server.

3 types of recovery model are available in the market , FULL, Simple and Bulk-Logged.

Recovery model maintain the total transaction log in SQL server. So if you have to protect data loss issue the suggestion is to use Full Recovery model, because it has the complete protection capability.

Replication is a technology of coping and distribution of data from one server to another.The benefits of Replication is improvement in data availability, user can easily browse or share the same data without any interruption.

3 types of Replication are available Snapshot Replication, Transactional Replication and Merge Replication.

• Snapshot Replication is used to take data snapshots in one server to relocate it to another server. It is the simplest replication process and used for small table works.
• Transactional Replication is used to copy data from main source to scatter between the users. Transactional Replication is used for duplicating sales order like tables.
• Merge Replication is the collector of data from multiple sources. It works with both Snapshot and Transaction Replication. Merge Replication is useful for the remote areas where network is unavailable for most of the time.

3 types of elements are available in Replication Publisher, Distributor, and Subscriber. The publisher is responsible for data publication. The distributor is responsible for catering the info objects to various sources. The subscriber is the element where the published subscription is copied.

Replication can be any of the following:

• Publisher, Distributor, and Subscriber are on the same SQL Instance.
• Publisher and Distributor are on same SQL Instance and Subscriber on a separate Instance.
• Publisher, Distributor, and Subscriber all are on individual SQL Instances.

First I would go to SQL Server Configuration Manager. Then click the left panel of the tool, I would select SQL Server Services, the right side panel displays all of the SQL Server Services/components that are installed on that machine. If the Service is displayed as (MSSQLSERVER), then it indicates it is a default instance, else there will be the Instance name displayed.

2 Authentication modes; Windows Authentication and SQL Server. Windows Authentication mode also referred to as Mixed Mode.

SQL Server 2005, installing failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. In SQL Server 2005, the Installation process itself installs all of the nodes (be it 2 nodes or 3 nodes) but in 2008 or 2008 R2 this process is modified, you have to install separately on every nodes, 2 times for two nodes and 3 times for three nodes.

The design of Active –Passive cluster is a failover cluster in which only one node is active and other node, called as the Passive node is always online but disabled. But if the   Active Node fails the Passive Node takes its place on the SQL Server Services and becomes the Active Node, the failed Active Node become Passive Node.

Design of an Active-Active cluster is a failover cluster in which both the cluster nodes are active at any given point in time. One Instance of SQL Server is running on every nodes of the cluster and if one amongst the nodes failed, both the Instances run on the only one node till the failed node is repaired. The instance will then back to its selected node.

Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Heartbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk, and MSDTC Disk.

Transparent Data Encryption is a mechanism newly added in SQL Server 2008 for coding and encryption to defend unauthorized access of information files of SQL Server. Also, TDE will protect the backup information of the instance.

No, Transparent Data Encryption doesn’t provide encryption at the time of data transmission.

• High-Safety Mode ensures that the Principal and reflected info area unit synchronization, transactions are committed at the same time on both servers to secure stable condition, but there is/might be a time lag.
• High-Performance Mode is to ensure that the Principal database run faster, without depending on the Mirrored database to commit the transactions. There is very little chance of loss of knowledge and the material behind the collectively reflective information can be insulated

Drop info deletes the info with its source file, without a backup you can’t recover it.  Offline database is not a deleted database only unavailable for user unless you back to online.

The better option is to change the Autogrowth database settings into Megabyte than Percentage.

Row compression and Page Compressionn.

2 types, In-place upgrade and Side-by-Side Upgrade.

Use Cluster Administrator to connect to the cluster and select the SQL Server cluster and select the SQL Server group, at the right side of the console, the “Owner” column indicates the proper node on which the SQL Server group is currently active.

From begin -> Run and sort CluAdmin (case insensitive) and therefore the Cluster Administrator console is displayed or you can even head to begin -> All Programs -> body Tools -> Cluster Administrator.

Go to Cluster Administrator then right click on the SQL Server Group and from the popup menu item choose to Take Offline.

• T-SQL; Create Database command.
• Using Management Studio
• Restoring a database backup
• Copy Database wizard

Two operational modes High-Safety Mode and Superior Mode.

Yes, it’s possible to own numerous configurations in a very Replication atmosphere.