Posts Tagged ‘VIA Protocol’

Discontinued Database Engine Functionality in SQL Server 2012

Posted: December 23, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , , ,
0

Here are Database Engine features that are no longer available in SQL Server 2012.

Category

Discontinued feature

Replacement
Backup and Restore

BACKUP { DATABASE | LOG } WITH PASSWORD and BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD are discontinued. RESTORE { DATABASE | LOG } WITH [MEDIA]PASSWORD continues to be deprecated.

None
Backup and Restore

RESTORE { DATABASE | LOG } … WITH DBO_ONLY

RESTORE { DATABASE | LOG } … … WITH RESTRICTED_USER
Compatibility level

80 compatibility levels

Databases must be set to at least compatibility level 90.
Configuration Options

sp_configure ‘user instance timeout’ and ‘user instances enabled’

Use the Local Database feature. For more information, see SqlLocalDB Utility
Connection protocols

Support for the VIA protocol is discontinued.

Use TCP instead.
Database objects

WITH APPEND clause on triggers

Re-create the whole trigger.
Database options

sp_dboption

ALTER DATABASE
Mail

SQL Mail

Use Database Mail. For more information, see Database Mail and Use Database Mail Instead of SQL Mail.
Memory Management

32-bit Address Windowing Extensions (AWE) and 32-bit Hot Add memory support.

Use a 64-bit operating system.
Metadata

DATABASEPROPERTY

DATABASEPROPERTYEX
Programmability

SQL Server Distributed Management Objects (SQL-DMO)

SQL Server Management Objects (SMO)
Query hints

FASTFIRSTROW hint

OPTION (FAST n).
Remote servers

The ability for users to create new remote servers by using sp_addserver is discontinued. sp_addserver with the ‘local’ option remains available. Remote servers preserved during upgrade or created by replication can be used.

Replace remote servers by using linked servers.
Security

sp_dropalias

Replace aliases with a combination of user accounts and database roles. Use sp_dropalias to remove aliases in upgraded databases.
Security

The version parameter of PWDCOMPARE representing a value from a login earlier than SQL Server 2000 is discontinued.

None
Service Broker programmability in SMO

The Microsoft.SqlServer.Management.Smo.Broker.BrokerPriority class no longer implements theMicrosoft.SqlServer.Management.Smo.IObjectPermission interface.
SET options

SET DISABLE_DEF_CNST_CHK

None.
System tables

sys.database_principal_aliases

Use roles instead of aliases.
Transact-SQL

RAISERROR in the format RAISERROR integer ‘string’ is discontinued.

Rewrite the statement using the current RAISERROR(…) syntax.
Transact-SQL syntax

COMPUTE / COMPUTE BY

Use ROLLUP
Transact-SQL syntax

Use of *= and =*

Use ANSI join syntax. For more information, see FROM (Transact-SQL).
XEvents

databases_data_file_size_changed, databases_log_file_size_changed

eventdatabases_log_file_used_size_changed

locks_lock_timeouts_greater_than_0

locks_lock_timeouts

Replaced by database_file_size_change event, database_file_size_change

database_file_size_change event

lock_timeout_greater_than_0

lock_timeout

SQL Server Network Interface/Protocol

Posted: August 31, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , ,
1

The SQL Server Network Interface (SNI) is a protocol layer that establishes the network connection between the client and the server. It consists of a set of APIs that are used by both the database engine and the SQL Server Native Client i.e SNAC

SQL Server has support for the following protocols:

➤
Shared memory: Simple and fast, shared memory is the default protocol used to connect from a client running on the same computer as SQL Server. It can only be used locally, has no configurable properties, and is always tried first when connecting from the local machine means The limitation is that the client applications must reside on the same machine where the SQL Server is installed.

➤
TCP/IP: TCP/IP is the most commonly / the most popular and common protocol widely used throughout the industry today. It communicates across interconnected networks and is a standard for routing network traffics and offers advanced security features.It enables you to connect to SQL Server by specifying an IP address and a port number. Typically, this happens automatically when you specify an instance to connect to. Your internal name resolution system resolves the hostname part of the instance name to an IP address, and either you connect to the default TCP port number 1433 for default instances or the SQL Browser service will find the right port for a named instance using UDP port 1434.

➤
Named Pipes: This protocol can be used when your application and SQL Server resides on a local area network. A part of memory is used by one process to pass information to another process, so that the output of one is the input of the other. The second process can be local (on the same computer as the first) or remote (on a networked computer). TCP/IP and Named Pipes are comparable protocols in the architectures in which they can be used. Named Pipes was developed for local area networks (LANs) but it can be inefficient across slower networks such as wide area networks (WANs). To use Named Pipes you first need to enable it in SQL Server Configuration Manager (if you’ll be connecting remotely) and then create a SQL Server alias, which connects to the server using Named Pipes as the protocol. Named Pipes uses TCP port 445, so ensure that the port is open on any firewalls between the two computers, including the Windows Firewall.

➤
VIA: Virtual Interface Adapter is a protocol that enables high-performance communications between two systems. It requires specialized hardware at both ends and a dedicated connection. Like Named Pipes, to use the VIA protocol you fi rst need to enable it in SQL Server Configuration Manager and then create a SQL Server alias that connects to the server using VIA as the protocol. This protocol has been deprecated and will no longer be available in the future versions of SQL Server.

Regardless of the network protocol used, once the connection is established, SNI creates a secure connection to a TDS endpoint

TDS Endpoint : Tabular Data Stream (TDS) Endpoint also known as TSQL.

For Details About SQL Server ENDPOINT , Very good explained blog is http://www.tobuku.com/docs/SQL%20Server%20Endpoint.pdf , Thanks for writer.