What would you do when an object that is referenced in several places and in several databases (Stored Procedures, Functions, etc.) is being moved to a different database or is being renamed due to a new object naming policy? Think of how much work this change would generate for you?
Wouldn’t it be nice if there was a way to take care of these changes automatically?
A synonym is an alternative name (think of it like an alias) for objects in SQL Server that provides a single-part name to reference a base object that uses long object names, a two-part, three-part, or even for-part names object names.
Synonyms can reference objects that exist on local or remote servers. They provide a layer of abstraction to protect client application from any changes made to the name or the location of the base underlying object. The binding between a synonym and its underlying object is strictly by name only which means the underlying object can be modified, dropped or dropped and replaced by another object. You can also perform any DML operations on a synonym which actually gets performed on the underlying table.
Synonyms can be very helpful in the above scenario. Once you create synonyms for objects, you can move or rename your base object without affecting its reference or use.
Synonyms can also be used for creating short-cuts for long object names or the object names used in four-part queries for example linked servers. (ex: ServerName.DatabaseName.OwnerName.ObjectName)
Synonym can be created on the following object types:
User tables (both permanent and temporary)
Views
Stored Procedures (T-SQL and CLR)
Extended Stored Procedures
Replication Stored Procedures
Replication Filter Procedures
Assembly Aggregate Functions (CLR)
Assembly Table Valued Functions (CLR)
Scalar Functions
Inline Table Valued Function, etc.
Lets now look at some of the examples on how to use Synonyms in SQL Server.
Creating a Synonym for an object:
CREATE SYNONYM CUST_ADD
FOR ADVENTUREWORKSLT2008R2.SALESLT.ADDRESS
GO
Query newly created Synonym:
SELECT * FROM [CUST_ADD]
GO
Deleting a Synonym:
DROP SYNONYM [CUST_ADD]
GO
Let’s now create a Synonym for a Linked Server object. Make sure the linked server is already established. Once the Synonym is created for Linked Server, you can refer to the linked server object using the Synonym (short name)
--CREATE A SYNONYM ON A LINKED SERVER OBJECT
CREATE SYNONYM CUST_ADD
FOR SQLTIPS.ADVENTUREWORKSLT2008R2.SALESLT.ADDRESS
GO
--QUERYING THIS SYNONYM
SELECT * FROM [CUST_ADD]
GO
--DROPPING THIS SYNONYM
DROP SYNONYM [CUST_ADD]
GO
Let’s now look at inserting data in to a table using a Synonym:
INSERT INTO [CUST_ADD] VALUES
(
'ONE WAY'
,'MICROSOFT WAY'
,'REDMOND'
,'WASHINGTON'
,'USA'
,98052
,NEWID()
,GETDATE()
)
Go
In the database mirroring environment Principal interacts with all the users but the mirror server only receives transaction log data from the principal server and other than that mirror database doesn’t do anything as its in RECOVERING state all the time. In this knowledge nugget, we will explore how you can use Mirror database for Reporting or Querying purpose.
What are Database Snapshots?
Database snapshot is a static, read-only, transaction-consistent snapshot of its source database as it existed at the moment of the snapshot creation. With Database Snapshots you can take advantage of the mirror database for high availability purposes to offload reporting. You can create a database snapshot on the mirror database and direct client connection requests to the most recent snapshot. You can create a database snapshot on the mirror database only when the database is fully SYNCHRONIZED.
As long as both mirror and principal server are communicating with each other, database snapshots can be accessible to clients. Keep in mind that as database snapshot is static, new data is not available. You must create new database snapshots periodically and have applications direct incoming client connections to the newest snapshot.
The new database is almost empty, but it grows over time as more and more database pages are updated for the first time. Because every snapshot on a database grows incrementally in this way, each database snapshot consumes as much resources as a normal database. Depending on the configurations of the mirror server and principal server, having an excessive number of database snapshots on a mirror database might decrease performance on the principal database. Therefore, it is recommended that if you can live without touching the mirror databases / servers, great and if not you can keep only a few relatively recent snapshots on your mirror databases.
What Happens During a Role Switch?
If role switching occurs, the database and its snapshots are restarted, temporarily disconnecting users. Afterwards, the database snapshots remain on the server instance where they were created, which has become the new principal database.
Users can continue to use the snapshots after the failover. However, this places an additional load on the new principal server. If performance is a concern in your environment than it is recommended that you create a snapshot on the new mirror database when it becomes available and redirect clients to the new snapshot, and drop all of the database snapshots from the former mirror database.
Creating a Database Snapshot of a user database
Lets create a snapshot on the mirror database, make sure you are connected to the mirror database:
USE MASTER;
CREATE DATABASE SQLCOMMUNITY_SnapSHOT_0400
ON (Name= 'SQLCOMMUNITY_Data', --This is the logical name of the data file of SQLCOMMUNITY database
FILENAME='H:\MSSQL\DATA\SQLCOMMUNITY_0400&.snp')
AS SNAPSHOT OF SQLCOMMUNITY
Go
You are now ready to use database snapshots for querying mirror databases.
How and where to view database snapshots?
In the object explorer, connect to the instance of Microsoft SQL Server and expand Databases and then expand Database Snapshots and select the snapshot you want to view.
Dropping a Database Snapshot
Once you are done working with database snapshots, you may drop the database snapshots the same way as you would any user databases:
-- Deleting a snapshot database
Use Master;
Drop Database SQLCOMMUNITY_SnapSHOT_0400
Go
I often get this question from the community and my answer to this question depends based on your network environment whether you are on a local computer with no networks or whether you are on a Local Area Network or whether you are on a Wide Area Network. I’ve tried to provide some guidance / recommendation below that works best for many SQL Server installations.
In order to connect to SQL Server, your client machine must have a network protocol enabled that can communicate with SQL Server database engine. By default, SQL Server can listen on 4 different protocols that come installed with SQL Server. However, not all the installed protocols will be enabled and you would need to enable them at the server using SQL Server Configuration Manager to be able to use them from your client machine.
The four network Protocols in SQL Server are:
Protocol
Description
Shared Memory
This is the simplest protocol of all the other protocols available in SQL Server. The limitation is that the client applications must reside on the same machine where the SQL Server is installed.
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
This is 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.
VIADo not use this as this is Deprecated.
Virtual Interface Adapter (VIA) protocol works with VIA hardware. This protocol has been deprecated and will no longer be available in the future versions of SQL Server.
Here are the Best Practices for using SQL Server Network Protocols:
What Protocol to use on a Local Server?
If you are running your server application on a local computer which also runs SQL Server 2008 then Shared Memory or Named Pipes protocol is your best choice. However, Named Pipes get loaded in kernel mode of the operating system and are much faster on the local machine.Note: This is only true if both the application and SQL Server are running on the same machine and if not then it would use Network Pipes that can be used over Local Area Networks.
What Protocol to use on a Local Area Network (LAN)?
As long as your Local Area Network is fast, both TCP/IP and Named Pipes would perform with the same speed. However, if there are more requests waiting to hit the server then Named Pipes may encounter pipe-busy error when trying to connect to SQL Server whereas, TCP/IP sockets doesnt experience this problem as it supports backlog queue for any number of requests.
What Protocol to use on a Wide Area Network (WAN)?
Data transmission using TCP/IP sockets is much more streamlined and has relatively less overhead compared to other network protocols. TCP/IP protocol can also take advantage of many performance features supported by the operating system that includes windowing, delayed acknowledgement which can be very helpful on slow networks.
Ever felt the need for knowing who is logging on to your SQL Server and at what time?
Ever felt the need to restrict specific users for certain time-period or firing a trace to track down user activity?
Ever felt like limiting the number of concurrent connections for specific users?
Well, you can do all that now with Logon Triggers.
Logon Trigger allows you to fire a T-SQL, or a stored procedure in response to a LOGON event. You may use logon trigger to audit and control users by tracking login activity, restricting logins from accessing SQL Server, or by limiting the number of sessions for specific logins. Logon Triggers are fired only after a login is successfully authenticated but just before the user session is actually established.
All messages originating from inside the trigger (ex: messages, errors) using the PRINT statement are sent to the SQL Server error log.
NOTE: If the user authentication fails for any reason, then the Logon triggers are not fired.
Below example shows you how you can create a Logon trigger and send a message to SQL Server error log as soon as any user logs in:
Creating a LOGON Trigger
CREATE TRIGGER OPS_LOGON
ON ALL SERVER
AFTER LOGON
AS
BEGIN
PRINT SUSER_SNAME() + 'HAS JUST LOGGED IN TO '+UPPER(LTRIM(@@SERVERNAME))+ 'SQL SERVER AT '+LTRIM(GETDATE())
END
GO
Limit a Login to 5 Concurrent Sessions
CREATE TRIGGER OPS_LOGON
ON ALL SERVER WITH EXECUTE AS [Microsoft\SALEEM]
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= [Microsoft\SALEEM] AND
(SELECT COUNT(*) FROM SYS.DM_EXEC_SESSIONS WHERE IS_USER_PROCESS = 1 AND ORIGINAL_LOGIN_NAME = [Microsoft\SALEEM]) > 5
ROLLBACK;
END;
Querying all Server Level Triggers
SELECT * FROM SYS.SERVER_TRIGGERS
GO
Dropping or Deleting OPS_Logon Server Level Trigger
I’m often asked on how to identify the number of CPU’s (physical) and NUMA Nodes (logical CPU’s) from SQL Server. Here’s a quick and easy way to obtain such information.
Run the below query on the instance of SQL Server:
SELECT
(cpu_count / hyperthread_ratio) AS Number_of_PhysicalCPUs,
CPU_Count AS Number_of_LogicalCPUs
FROM sys.dm_os_sys_info
Running Antivirus software is critical part of server security but it is also important to understand the impact and effect it has on SQL Server. Imagine this: You’ve installed Anti-Virus on a server that already has SQL Server running, once the server reboots, it is highly likely that the Anti-virus software depending upon the scanning schedule will scan the SQL Server folders and lock SQL Server files before SQL Server can gain access to those files.
When Anti-Virus locks SQL Server files, SQL Server service would not only be able to access those files but there’s a possibility of data corruption since both SQL Server and the Anti-Virus programs are trying to access the files by putting an exclusive lock on them. You must prevent from getting into this situation. (At least on Production server)
Many DBAs would simply exclude .MDF and .LDF files from being scanned by Anti-Virus but thats not enough. There are other important files that can also have an impact on SQL Server.
Here’s the recommended list of items that needs to be excluded from all Anti-virus scans:
SQL Server Data Files (files with extensions like .MDF, .NDF)
SQL Server Log Files (files with extensions like .LDF)
SQL Server Backup files (files with extensions like .BAK and .TRN)
Full-Text Catalog Files
SQL Server Audit Files
SQL Server Query Files (Extensions like .SQL)
Trace/Profiler Files (Extensions like .TRC)
Entire Directory that is holding Analysis Services files that is used for processing Cubes and used for queries that are reading files in to the data folder and the temp folder.
Folder where Analysis Services Backups Files Reside
Exclude SQLServer.Exe file (This is the main database engine file)
ReportingServicesService.Exe
MSMDSrv.exe
Cluster Directory (Usually it is C:\Windows\Cluster) *If your servers are clustered
Cluster Quorum Drive
FILETABLE and Filestream folders
Database Mail file/folders
Taking care of excluding the above files/folders will make sure your Anti-Virus software doesnt interfere with any SQL Server operations. However, keep in mind that you may experience some slowness when running Anti-Virus software on the same server as SQL Server but at-least it will not corrupt your data and will not lock any SQL Server operations.
Recent Comments