Top Answers to SQL Server Interview Questions
|Platform Supported||Windows Server||All Unix & Linux|
|Replication done||Only databases||Instances & databases|
|Ease of use||Good||Average|
To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this launch SQL Server Configuration Manager and do the following: select the instance of SQL Server, right click and select Properties. After selecting properties you will just set Hide Instance to “Yes” and click OK or Apply. After the change is made, you need to restart the instance of SQL Server to not expose the name of the instance.
Yes. Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer. Starting with SQL Server 2008, SQL Server supports hot add CPU.
• Requires hardware that supports hot add CPU.
• Requires the 64-bit edition of Windows Server 2008 Datacenter or the Windows Server 2008 Enterprise Edition for Itanium-Based Systems operating system.
• Requires SQL Server Enterprise.
• SQL Server cannot be configured to use soft NUMA
Once the CPU is added just run RECONFIGURE then sql server recognizes the newly added CPU.
Learn more about SQL Server in this insightful blog now!
TELNET PAXT3DEVSQL24 1433
TELNET PAXT3DEVSQL24 1434
MSSQL Server: 1433
HTTP TCP 80
HTTPS TCP 443
Single User Mode (-m): sqlcmd –m –d master –S PAXT3DEVSQL11 –c –U sa –P *******
DAC (-A): sqlcmd –A –d master –S PAXT3DEVSQL11 –c –U sa –P *******
Emergency: ALTER DATABASE test_db SET EMERGENCY
- Instantiating COM objects is a time consuming process and since it is done within a trigger, it impedes the data insertion process. Same is the case with sending emails from triggers. This rundown can be better implemented by logging all the necessary data into a separate table, and have a job which checks this table and does the needful.
- Preferring NT authentication
- Using server, database and application roles to control access to the data
- Securing the physical database files using NTFS permissions
- Using an unusable SA password, restricting physical access to the SQL Server
- Renaming the Administrator account on the SQL Server computer
- Disabling the Guest account, enabling auditing using multiprotocol encryption,
- Setting up SSL, setting up firewalls, isolating SQL Server from the web server
These are described in SQL Server’s online reference guide and on SQL Server community.
Connect using DAC via CMD or SSMS
- Connect via CMD
- SQLCMD -A –U myadminlogin –P mypassword -SMyServer –dmaster
- Once you connect to the master database run the diagnostic quires to find the problem
- Correct the issue and restart the server
Some of the third-party tools are:
- SQL CHECK – Idera – Monitoring server activities and memory levels
- SQL DOC 2 – RedGate – Documenting the databases
- SQL Backup 5 – RedGate – Automating the Backup Process
- SQL Prompt – RedGate – Provides IntelliSense for SQL SERVER 2005/2000,
- Lite Speed 5.0 – Quest Soft – Backup and Restore
Benefits of using third-party tools:
- Faster backups and restores
- Flexible backup and recovery options
- Secure backups with encryption
- Enterprise view of your backup and recovery environment
- Easily identify optimal backup settings
- Visibility into the transaction log and transaction log backups
- Timeline view of backup history and schedules
- Recover individual database objects
- Encapsulate a complete database restore into a single file to speed up restore time
- When we need to improve upon the functionality that SQL Server offers natively
- Save time, better information or notification
Hotfixs are software patches that were applied to live systems (the ones still running.) A hotfix is a single, cumulative package that includes one or more files used to address a problem in a software product (i.e. a software bug).
In Microsoft SQL SERVER context, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes.
Ex: If a select query returns duplicate rows with aggregations, the result may be wrong….
In the SHRINKFILE command, SQL Server isn’t careful about where it puts the pages that are moved from end of the file to open pages towards the beginning of the file.
- The data becomes fragmented – potentially up to 100% fragmentation, and hence, it is a performance killer for your database;
- The operation is slow – all pointers to / from the page / rows being moved have to be fixed up, and the SHRINKFILE operation is single-threaded, so it can be really slow (the single-threaded nature of SHRINKFILE is not going to change any time soon)
- Shrink the file by using Truncate Only: First it removes the inactive part of the log and then perform shrink operation
- Rebuild / Reorganize the indexes once the shrink is done so the Fragmentation level is decreased.
Learn more about data structure in SQL Server programming tutorial.
- AES (256 bit)
- If we choose longer key, then encryption will be better, so choose longer keys for more encryption. However there is a larger performance penalty for longer keys. DES is a relatively old and weaker algorithm than AES.
- AES: Advanced Encryption Standard
- DES: Data Encryption Standard
Port 445. Named pipes communicate across TCP port 445.
- We store this data as XML based files, not in the MSDB database.
- With the configuration files, you can point the packages from prod to dev (and vice versa) in just few seconds.
- The packages and config files are stored in a directory of your choice.
- Resources permitting create a standalone SSIS server away from the primary SQL Server
Open Cluster Administrator checks the SQL Server group where you can see the current owner. So current owner is the active node and other nodes are passive
Deadlock Information: 1204, 1205, 1222
Network Database files: 1807
Log Record for Connections: 4013
Skip Startup Stored Procedures: 4022
Disable Locking Hints: 8755
Forces uniform extent allocations instead of mixed page allocations 1118 – (SQL 2005 and 2008) To reduces TempDB contention.
Become Master of SQL Server by going through this online SQL Server training.
No way. The only way if you want to get a backup by using windows backup for option resource mdf and ldf files.
Upgrade Advisor can analyze remote instances of SQL Server, except for SQL Server Reporting Services. To analyze Reporting Services, Upgrade Advisor must be installed and executed on the report server.
The safest approach is the side-by-side upgrade. You can do this either by using backup and restore approach or detach/attach of the database files. I’d suggest using the backup & restore as the safer approach. The steps are:
- Run Upgrade Analysis tool from Microsoft. Address any issues raised there, first.
- Identify DTS packages. These must be migrated by hand, unless you buy Pragmatic Works excellent software. Rebuild the DTS packages as SSIS.
- Script out all SQL Agent jobs.
- Script out all security
- Backup the systems and validate the backups (preferably by restoring them to another system)
- Run the security script on the new system
- Run the restore on the new system.
- Validate the databases by running DBCC
- Manually update all statistics
- Run the SQL Agent script
Yes. SQL Server 2005 databases are compatible with SQL Server 2008. Attaching a SQL Server 2005 database to SQL Server 2008 automatically upgrades the SQL Server 2005 database to the latter and the database is then no longer usable by the SQL Server 2005 installation.
No. The only way to move a SQL Server 2008 database to a SQL Server 2005 server is by transferring the data using a method such as Data Transformation Services (Import/Export), SSIS, bcp, or use of a query between linked servers.
If applications or services have open ODBC connections to the SQL Server 2005 during the conversion process, they may not allow the SQL Server to shut down completely. The conversion process will not proceed to the next step if it does not receive verification that the SQL Server has been completely stopped.
If the legacy SQL Server instance is replaced by a new SQL Server 2008 instance, rolling back an in-place upgrade can be complex and time-consuming, whereas in a side-by-side upgrade the legacy instance remains available if a rollback is needed.
The below command enforces an exclusive lock on database, which makes the process faster
DBCC CHECKDB (‘TestDB’) WITHNO_INFOMGS, TABLOCK
This command limits checking the integrity of the physical structure of the page and record headers and can also detect torn pages, checksum failures, and common hardware failures.Using PHYSICAL_ONLY option may cause shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems. Specifying PHYSICAL_ONLY causes DBCC CHECKDB to skip all checks of FILESTREAM data.DBCC CHECKDB (‘TestDB’) WITH NO_INFOMSGS, PHYSICAL_ONLY
Below command causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range.DBCC CHECKDB (‘TestDB’) WITH NO_INFOMSGS, DATA_PURITYIn this command, DBCC CHECKDB detects columns with date and time values that are larger or less than the acceptable range for the datetime data type. It also limits the checking of integrity of the physical structure of the page and record.
Within either of these isolation levels, locks are held for the duration of the transaction unlike within the READ_COMMITTED isolation level.
- First is the READ COMMITTED isolation level. This is the only level that supports both a pessimistic (locking-based) and optimistic (version-based) concurrency control model.
- Second is SNAPSHOT isolation level that supports only an optimistic concurrency control model.
READ_COMMITTED_SNAPSHOT option for the read committed optimistic model. ALLOW_SNAPSHOT_ISOLATION option for the snapshot isolation level.
The Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource (lower in the lock hierarchy.) Intent locks are named because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level. Intent locks serve two purposes:
- To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
- To improve the efficiency of the Database Engine in detecting lock conflicts at higher level of granularity.
The Graphical Execution Plan should be read from Right to Left.
- Check the Graphical execution plan of a stored procedure / Query
- Table Scan – Index is missing
- Index Scan – Proper indexes are not using
- BookMark Lookup – Limit the number of columns in the select list
- Filter – Remove any functions from where clause; may require additional indexes
- Sort – Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?
- DataFlow Arrow – High density: Sometimes you find few rows as outcome but the arrow line density indicates the query/proc processing huge number of rows
- Cost – Can easily find out which table / operation taking much time
- From the execution plan, we can find out the bottlenecks and give the possible solution to avoid latency
Either the user must be mapped to sysadmin, db_owner, db_creator or they will be granted the permission, “Show Plan”.GRANT SHOWPLAN TO [username].
This blog will help you get a better understanding of SQL Optimization Techniques!