Tuesday, September 14, 2010

SQL Server Database Mirroring Pros and Cons

Tuesday, September 14, 2010  Nareshkar Pakanati  No comments

Here is the list of advantages and disadvantages of Database mirroring in SQL Server 2008.

Point 1: Database mirroring can be configured along with replication, log shipping, and database snapshots. 

Point 2: In SQL Server 2008 compresses the Transaction Log at Principal Server before it is transferred to mirror server so it saves lot of traffic bandwidth. 

Point 3: System databases can not be mirrored. 

Point 4: Database which needs to be mirrored it must be a FULL recovery mode, mirroring doesn't work on other recovery models. 

Point 5: High Safety Mode (Synchronous operation) Log are committed on Principal and Mirror when databases are synchronized. 

Point 6: High Performance Mode (Asynchronous operation) Principal commits and Mirror tries to keep up with received log records from Principal. 

Point 7: The mirrored server can not be used during mirroring operation, it always be on recovery mode. 

Point 8: We can take the snapshot of the mirrored database, for reporting purpose.

Point 9: Automatic Failover is only possible when high safety mode is configured with automatic failover.

Point 10: Database mirroring has automatic server failover and client failover capabilities.

Point 11: Configuration of database mirroring is simpler than any other high availability like log shipping and replication.
 
Point 12: Mirroring has built-in network encryption support (AES algorithm).
 
Point 13: Database mirroring supports full-text catalogs.

Point 14: If you are configuring witness server as well then additional SQL Server instances may be required.
Pros and Cons for Database . Basic about database mirroring. Evaluating SQL Server Database Mirroring as a Disaster Recovery. Reasons not to configure automatic failover for database mirroring. Database Mirroring vs Failover Clustering. SQL Server 2012 Database Mirroring. Mirroring vs. Log Shipping in Sql Server 2008 R2. Database Mirroring in the Real World. Database Mirroring Advantages & Disadvantages. SQL Server High Availability Options. Pros and cons of HA solutions. Database Mirroring or Clustering. Database Mirroring Architecture. SQL Mirroring Cons. Database Mirroring Issue. Database Mirroring error.

Read More

Friday, September 10, 2010

Server Roles In SQL Server

Friday, September 10, 2010  Nareshkar Pakanati  No comments

Fixed server roles are Server level Securables.

Here is a list of Fixed Server Level Roles In SQL Server 2005

PROCESS ADMIN: Process Admin manages the processes, running in an Instance of SQL Server

SERVER ADMIN: Server Admin role is used to configure the server wide settings.

SECURITY ADMIN: Security Admin role is used to manage the server Logins

SETUP ADMIN: Setup Admin role is used to add or remove linked servers

DISK ADMIN: Disk Admin role is used to manage the disk files

BULK ADMIN: Bulk Admin role is used to execute bulk insert statements

SYS ADMIN: Sys Admin role is used to take full control of the server system

DB CREATOR: Database Creator role is used to create and alter databases

DB OWNER: By using DB Owner role we can have full control of the database

PUBLIC: By using public role, we can view and expand the databases
Understanding roles in SQL Server security. Understanding SQL Server fixed server roles. New User-defined Server Roles in SQL Server 2012. SQL Server roles. Create Your Own Fixed Server Roles In SQL Server. SQL Server Security: Server Roles. SQL Server Role Membership. sql server server role for backups. sql server server role public. sql server server role sysadmin. Removing a SQL Server Database Role. Setting up users and roles in SQL Server. SQL Server Agent Fixed Database Roles. CREATE ROLE . Transact-SQL. CREATE SERVER ROLE. ALTER SERVER ROLE. Security Role Requirements for Replication. Server-Level Roles. User Defined Server Role on SQL Server 2012. Add SQL 2012 custom Server role to allow and deny certain tasks. SQL Server User Defined Server Roles. SQL Server 2008 server role.

Read More

What's the use of SET NOCOUNT ON

Friday, September 10, 2010  Nareshkar Pakanati  No comments

What's the use of SET NOCOUNT ON?

SET NOCOUNT ON eliminates the sending of the messages to the client for each statement in a stored procedure, like "10 row(s) effected" if you select or update data. For example when SET NOCOUNT is OFF or nothing defined it will shows like below below Image.
 
When SET NOCOUNT is ON, the count will not be returned back to client.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON. When SET NOCOUNT is OFF, the count is returned. 

NOTE: The setting of SET NOCOUNT is set at execute or run time and not at parse time.
SET NOCOUNT Transact-SQL. SET NOCOUNT. Effect of SET NOCOUNT ON. SET NOCOUNT ON usage. A simple Performance Booster in sql server. What is the use of SET NOCOUNT ON in SQL Server. what happen when we ON set no count. set nocount on in sql server 2008. set nocount on in sql server 2012. set nocount on stored procedure. set nocount on means. set nocount on in oracle. set nocount on in sql. set nocount on in sybase. set nocount on and off. set nocount on in sql server.

Read More