|
Microsoft SQL Server Interview Questions and Answers
What level of permissions does a user need to create
an assembly with UNSAFE permissions?
sysadmin
Only a sysadmin can create assemblies that are specified
as unsafe.
Which optional SQL Server component must you install to
ensure that you can create and execute maintenance
plans?
SQL Server Integration Services
You must install Integration Services in order to
properly create and execute maintance plans since these
plans are a subset of SSIS packages.
You have tightly secured access to your SQL Server 2005
server by placing it behind a firewall and limited ports
1433 to those clients that need to directly access the
SQL Server. One of your developers complains about
connecting to the Integration Services esrvice on your
server. What could be the problem if you know the
service is working properly?
The firewall needs to allow port 135 through for
Integration Services
Integration Services requires access to both DCOM and
port 135. You need to open this port to allow the
developer to communicate with SSIS.
In SQL Server 2005, which network protocols use
endpoints?
All of the above
All communication points with SQL Server using network
protocols use an endpoint. SQL Server creates defaults
for each protocol that you enable.
You have a SQL Server 2005 cluster and need to add
anti-virus software as per your corporate standards.
What should you exclude from scans?
Log and data files for your databases as well as the
Quorum drive and the backup folders.
Anti-virus programs can exist on the same server as SQL
Server without an issue if you exclude certain items.
The database data and log files, backup files and
folders, the quorum drive for clusters, temporary
replication files, the SQL Server log files, and Log
Shipping folders/files. You do not need to exclude that
SQL Server executables and probably do not want to so
that they are protected.
What port do you need to open on your server firewall to
enable named pipes connections?
Port 445
Named pipes communicates across TCP port 445.
When the network service built-in account connects to a
network resource, what is the context?
The computer account of the Windows installation.
The network service account connects to network
resources as the computer account for the Windows
installation.
A certificate is like which type of key in SQL Server
2005 encryption?
An Asymmetric Key
A certificate is like an asymmetric key and can validate
the identity of the issuer.
How many connections are available in SQL Server MSDE
2000?
Unlimited, no different from the other SQL Server 2000
editions.
The answer is actually an unlimited number of
connections, though this in a practical sense is limited
by memory on the machine (The actual limitation is 32k
connecitons). Instead there is a governer that kicks in
to limit things to 8 operations, of which 3 are system
operations in some cases. But these are operations, not
connections.
What will be the result of the last SELECT statement
if you execute the T-SQL script below as a sysadmin.
USE master
GO
IF DB_ID('test_schema') IS NOT NULL
DROP DATABASE test_schema
GO
CREATE DATABASE test_schema
GO
USE test_schema
GO
CREATE SCHEMA Schema1
CREATE TABLE Table1 (col1 int)
GO
CREATE SCHEMA Schema2
GO
CREATE TABLE Table1 (col1 int)
GO
SELECT SCHEMA_NAME(schema_id), name FROM sys.objects
WHERE name = 'Table1'
Schema1 Table1
dbo Table1
The correct answer is A. You can combine the CREATE
SCHEMA with a CREATE TABLE. You can even specify GRANTs
or DENYs. Search Books online for CREATE SCHEMA.
You are considering consolidating a number of your
existing SQL Servers onto a new, large SQL Server 2005
server. You have 3 SQL Server 6.5 servers, 6 SQL Server
7.0 servers, and 16 SQL Server 2005 servers. You cannot
make any application changes, but you can size a large
SQL Server 2005 server to handle the disk, memory, and
CPU needs of the existing servers as well as set
compatibility modes. How many of these servers can you
consolidate?
25
In SQL Server 2005, you can handle a large number of
transactions, which makes it a good choice for
consolidating servers. It includes compatibility modes
for SQL Server 6, 6.5, 7 and 2000. Therefore you can
consolidate all the servers, 25 of them, onto the new
server.
To maintain the best performance of your SQL Server 2005
databases, how should you deploy encryption
technologies?
Encrypt data with a symmetric key and secure all keys
with asymmetric keys.
For performance reasons, you should encrypt data using a
symmetric key and secure that key with an asymmetric
key.
In SQL Server 2005, which network protocols use
endpoints?
All of the above
All communication points with SQL Server using network
protocols use an endpoint. SQL Server creates defaults
for each protocol that you enable.
Page Numbers : 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
Job Interview Questions
for more Interview Questions with Answers
|
