What is Network Packet Size in SQL?
NPS(Network Packet Size) is the size of the TDS (tabular data stream) packets used to communicate between your applications and your relational database engine and default packet size is 4 kilobytes and its manily depends on network packet size configuration option.
How do I resolve ‘SQL Server Setup cannot valid the service accounts’ error message during the setup?
The error message typically happens when you use domain accounts as service account. If the Activity Directory Service (ADS) is not available currently, so the setup cannot valid the domain accounts you specify.
What is difference between OSQL and Query Analyzer ?
Both are the same but there is little difference OSQL is command line tool which is execute query and display the result same a query analyzer but query analyzer is graphical and OSQL.
OSQL have not ability like query analyzer to analyze queries and show static on speed of execution and other useful thing about OSQL is that its helps in scheduling.
How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL is a command line tool. Server in single user mode and -f is used to start the SQL Server in minimal configuration mode.
What is the use of the Public database role in SQL Server?
Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.
Explain Certificate based SQL Server Logins/Principals?
Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.
What are steps to load a .NET code in SQL SERVER ?
Write the managed code and compile it to a DLL/Assembly.
After the DLL is compiled using the “CREATE ASSEMBLY” command you can load the assemby into SQL SERVER. Below is the create command which is loading “mycode.dll” into SQL SERVER using the “CREATE ASSEMBLY” command.
CREATE ASSEMBLY AssemblyName FROM ‘C:/MyAssmbly.dll’
How can we drop an assembly from SQL SERVER?
DROP ASSEMBLY AssemblyName
Are changes made to assembly updated automatically in database?
No: it will not synchronize the code automatically. For that you have to drop the assembly (Using DROP ASSEMBLY) and create (Using the CREATE ASSEMBLY again).
If I want to see which files are linked with which assemblies?
Use sys.Assemblies_files system tables have the track about which files are associated with what assemblies.
Does .NET CLR and SQL SERVER run in different process?
.NET CLR engine (hence all the .NET applications) and SQL SERVER run in the same process or address space. This “Same address space architecture” is implemeted so that there no speed issues. If the architecture was implemented the other way (i.e. SQL SERVER and .NET CLR engine running in different memory process area) there would have been reasonable speed issue.
What is SQL Server VSS Writer?
The SQL Writer Service provides added functionality for backup and restore of SQL Server through the Volume Shadow Copy Service framework. When running, Database Engine locks and has exclusive access to the data files.
When the SQL Writer Service is not running, backup programs running in Windows do not have access to the data files, and backups must be performed using SQL Server backup.
Use the SQL Writer Service to permit Windows backup programs to copy SQL Server data files while SQL Server is running. It must be running when the Volume Shadow Copy Service (VSS) application requests a backup or restore. To configure the service, use the Microsoft Windows Services applet. The SQL Writer Service installs on all operating systems.
What issues SQL Server Upgrade Advisor helps you to identify that might affect your upgrade to latest version of SQL Server?
SQL Server Upgrade Advisor can help identify key issues that might affect your upgrade, such as deprecated or discontinued features, breaking changes and behaviour changes.
What is the quickest way to identify the cause of SQL Server Installation failures?
You can do that by reviewing the summary log files and individual component log files. If you review the summary log file and find installation errors, you should review the specific log file for that component for more information.
Briefly explain the purpose of SQL Server Reporting Services (SSRS)
You create reports that include content from a variety of data sources, including relational databases and OLAP databases with SQL Server Reporting Services (SSRS). You can format the reports on-demand by using a variety of formats, including Microsoft Excel, HTML, and PDF.
You can generate ad hoc (on-the-fly) reports or schedule them for recurring distribution. The primary tools you use to create reports are Report Builder and SQL Server Data Tools (SSDT) – Report Designer.
What is the purpose of SQL Server Full-Text Search Service?
You can query for words and phrases in columns in the database with SQL Server Full-Text Search. You can search for multiple forms of a word. SQL Server Full-Text Search is implemented by the SQL Server Database Engine.
What is Bound Connection?
Bound connections allow two or more connections to share the same transaction and locks. Bound connections can work on the same data without lock conflicts.
Bound connections can be created from multiple connections within the same application, or from multiple applications with separate connections. Bound connections make coordinating actions across multiple connections easier.