This article is half-done without your Comment! *** Please share your thoughts via Comment ***
List out Dynamic Management Views (DMV’s) and the value that they provide to developers or DBAs.
- dm_tran_locks – Locking and blocking.
- dm_clr_loaded_assemblies – Assemblies in available in SQL Server.
- dm_db_file_space_usage – Database file usage to determine if databases are getting low on space and need immediate attention.
- dm_exec_cached_plans – Cached query plans available to SQL Server.
- dm_exec_sessions – Sessions in SQL Server.
- dm_exec_connections – Connections to SQL Server.
- dm_db_index_usage_stats – Seeks, scans, lookups per index.
- dm_io_virtual_file_stats – IO statistics for databases and log files.
- dm_broker_connections – Service Broker connections to the network.
- dm_os_memory_objects – SQL Server memory usage sys.dm_tran_active_transactions – Transaction state for an instance of SQL Server.
Please explain the relationship between logins and users in the system and user databases.
Logins – All logins reside in the master database
Users – All users reside in the master database, other system databases and in the user defined databases.
Briefly explain the purpose of SQL Server Integration Services (SSIS)?
SQL Server Integration Services (SSIS) is a platform for data integration and workflow applications. It was first introduced with SQL Server 2005 replacement for Data Transformation Service (DTS). You build extraction, transformation, and load (ETL) packages with SSIS that extract, transform, and load data. A package is an SSIS object that defines the data source, transformation, and data destination used to integrate data from diverse data sources.
SSIS supports a variety of formats that you can use as the data source when you import data. These formats include: Microsoft Excel, Flat files, OLE-DB data sources, XML data sources etc. SSIS also supports SQL Server and these formats as data destination formats.
You have a default instance for SQL Server 2012 running on one computer. You install SQL Server 2014 on a different computer. You want to migrate the user databases from SQL Server 2012 to SQL Server 2014. What should you do?
You should register the SQL Server 2012 instance in SQL Server 2014 Management Studio and use the Copy Database Wizard to migrate the database.
You are upgrading a default instance of SQL Server 2012 to SQL Server 2014. What will be used as the SQL Server service account?
The service account specified for the SQL Server 2012 instance will be used as the SQL Server 2014 service account.
If you encounter this kind of an error message, what you need to look into to solve this problem?
[Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL Server not found.
Check if MS SQL Server service is running on the computer you are trying to log into.
Check on Client Configuration utility. Client and Server have to in sync.
What kind of LAN types do you know?
Ethernet networks and token ring networks.
What is SQL Azure?
SQL Azure is a cloud based relational database as a Service offered by Microsoft. Conceptually it is SQL server in the cloud.
How can we migrate from SQL server to SQL Azure?
For Data Migration, we can use BCP or SSIS. And for schema Migration we can use Generate Script Wizard. Also, we could use a Tool called SQL Azure migration wizard available on codeplex.
What is the system function to get the current user’s user id?
USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().
What are the built-ins used for processing rows?
- Get_group_row_count(function)
- Get_group_selection_count(function)
- Get_group_selection(function)
- Reset_group_selection(procedure)
- Set_group_selection(procedure)
- Unset_group_selection(procedure)
How many files can a Database contain in SQL Server?
A Database can contain a maximum of 32,767 files.
How do I troubleshoot SQL Server connectivity issues?
- Check your SQL Server Error log to make sure that it enables TCP/IP protocol and establish the listening on a specific TCP port.
- Enable TCP/IP protocol if you could not find the message in the Error Log.
- Check firewall if you cannot telnet to the specific TCP port.
- Ensure that the TCP port on which SQL Server is listening has been put into the exceptions of your firewall.
What is the maximum limit of SQL Server instances for a standalone computer?
50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster.
Why did a T-SQL script fail to run a job when it could successfully run in SQL Server Management Studio ?
If the SQL Server Agent Service account is different from the login account and if it does not have enough permission to execute the query, the job will fail.
You can simply give the SQL Server Agent Service account permissions to execute the query, this is not the best way from a security perspective. For SQL Server Agent Service account, it is recommended to give it the least amount of privileges.
How can I degrade a SQL Server database from a higher version to a lower one?
Yes, degrading a SQL Server database is not supported, but you can use a flexible workaround to resolve this issue. You can use SQL Server Management Studio to first generate scripts for a lower ersion and then use Data Import/Export Wizard to transfer data.
What is the difference between dropping a database and taking a database offline?
Drop database deletes the database along with the physical files, it is not possible to bring back the database unless you have a backup of the database.
When you take a database offline, you the database is not available for users, it is not deleted physically, it can be brought back online.
