Advantages and Disadvantages of Database Mirroring

Advantages Database Mirroring:
o Database Mirroring architecture is more robust and efficient than Database Log Shipping. It can be configured to replicate the changes synchronously to minimized data loss.
o It has automatic server failover and client failover mechanism.
o Configuration is simpler than log shipping and replication, and has built-in network encryption support (AES algorithm).
o Because propagation can be done asynchronously, it requires less bandwidth than synchronous method (e.g. host-based replication, clustering) and is not limited by geographical distance with current technology.
o Database mirroring supports full-text catalogs.
o Does not require special hardware (such as shared storage, heart-beat connection) and clusterware, thus potentially has lower infrastructure cost.

Disadvantages of Database Mirroring:
o Potential data lost is possible in asynchronous operation mode. RTO will vary and depend on several factors, such as propagation interval time and bandwidth speed.
o Mirror server/database is not available for user operation.
o It only works at database level and not at server level. It only propagates changes at database level, no server level objects, such as logins and fixed server role membership, can be propagated.
o Automatic server failover may not be suitable for application using multiple databases.

Database Mirroring FAQ

Database Mirroring FAQ:

1. How to enable database mirroring?
Answer: You can use TRACE 1400 to enable in RTM version or else update your SQL Server with latest service pack.
Adding Trace Flag to Startup parameter
o Goto RUN --> Type sqlservermanager.msc
o Right click on SQL Server(instancename) service and click on properties
o Click on Advanced tab
o In the startup parameters enter this ;-T1400 and click on OK
o Restart SQLservices and then try configuring db mirroring
Or
o Update SQL Server to latest service pack.
2. IS it possible to configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?
Answer:
Nope its not possible, both principal and mirror should have same edition.

3. Can I load balance my mirrored database (i.e can i use mirrored for SELECT query) like log shipping in standby mode?
Answer:
It is possible if your mirror server is running Enterprise edition. You can take snapshot of your mirrored database and then you can query against the snapshot to retrive data's.

4. Is it possible to take backup of mirrored database in mirror server?
Answer:
No, you won't be able to run BACKUP command against a mirrored database in mirror server.

5. Can I create multiple endpoints for configuring different databases for mirroring and point each database to unique endpoint.
Answer:
No, thats not possible. You can create only one endpoint in a server for database mirroring you need to use this endpoint to configuring db mirroring for all the databases.

6. Can I configure a single database to be mirrored to more than one server. i.e) One source & many destination like logshipping.
Answer:
No, thats not possible in Database mirroring, its one to one configuration.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

Steps to resolve above error.
1) SQL Server should be up and running.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if SQL Server service status is “Running”.


2) Enable TCP/IP in SQL Server Configuration

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select TCP/IP

3) Open Port in Windows Firewall

All the ports on which SQL Server is running should be added to exception and firewall should filter all the traffic from those ports.By default SQL Server runs on port 1433, but if default port is changed then the new port should be added to exception.
Go to Control Panel >> Windows Firewall >> Change Settings >> Exceptions >> Add Port

4) SQL Server browser service should be up and running
If SQL Server has named instance (another instance besides default instance) is installed, SQL Server browser service should be up and running and also be added to the exception, as described in Step 3.

5) Enable Remote Connection
If this feature is turned off SQL Server will function smoothly on local machine, but it will let another server connect to it remotely. By default this feature is ON in SQL Server 2008.

Things to Do in a Database

Things to Do in a Database:

1.Have and exercise a Disaster Recovery Plan
2. Do not modify a production database inappropriately
3. Do not modify existing objects using ad-hoc queries
4. Always Develop new features in development first and then use in production.
5. Normalize your data
6. Enforce Foreign Key Relationships
7. Document a Database Schema
8. Have and implement a data retention policy
9. Tune a database
10. create and maintain indexes based on data access patterns
11. create and maintain appropriate statistics
12. implement appropriate security
13.Utilize Schema's. do not use DBO schema for all roles, use different schemasa for separating roles
14.Separation Data Access
15. Associate Authentication and User with appropriate roles and schema's
16.Review Application Logs

Database Design Essentials

The components that are absolutely necessary for a successful database project. There are 10 essentials:

Understand the database’s purpose:
The first essential is that you discover and understand the database’s purpose before doing anything else. Saying that the database’s purpose is “to store records” isn’t good enough. You need to understand the business reason for this database’s existence before it goes into development, much less production. The business reason needs to map not only to the technical specifications but also to the business purpose and the business processes that this database will be supporting.

Get the right tool:
A good CASE tool can help you quickly create different types of data models—such as functional decompositions, system and context diagrams, business process and data flow diagrams, and entity relationship models—each of which will help you communicate to your colleagues the visual components of the proposed system you’re building.

Gather the requirements for the database.
You need to understand the requirements well enough to be able to create visual representations of data, processes, and systems.

Be conscientious when modeling the data:
You need to perform data modeling at multiple levels. You should create the following three models:

Conceptual data model (CDM). The CDM contains the semantics of the information domain that you’re modeling. The CDM defines the scope of the database. Entity classes—the distinct data sets I mentioned earlier—represent things of interest to the organization, and the relationships between the entity pairs assert business rules and regulations. The CDM expresses both the as-is and to-be states and is often created during the Discovery stage.
Logical data model (LDM). The LDM describes the structure of the information domain. It includes criteria such as attribute set details, data types, key status, and individual attribute nullability.
Physical data model (PDM).The PDM defines how the data will be physically stored. You need to map the LDM to a specific database management system (DBMS) platform.

Enforce relationships.
The whole idea of a relational database is to identify and enforce relationships between entity pairs so that data integrity, as defined in the business rules and regulations and represented in the data models, is maintained.

Use the appropriate data types.
Defining appropriate data types and lengths, nullability, defaults, and check constraints when creating tables
Using user-defined types, user-defined functions, and stored procedures that evaluate data when it’s being entered or used
Using Declarative Referential Integrity (DRI) or triggers to enforce relationships between pairs of tables
Using lookup tables to restrict data values when they’re inputted or edited

Include indexes when modeling.:
From the gathered requirements, you should be able to determine which columns will be used for filtering (i.e., in the WHERE clause),sort ( ORDER BY clause)


Standardize the naming convention.
Store the code that touches the data in Server.
The best way to avoid inconsistent rule application and SQL injection is to store code that touches a database’s data as a stored procedure or CLR object.
Document the work.

Performance Counters for SQL server DBA

Available Mbytes

Definition

Available Mbytes stands for free unallocated RAM and displays the amount of physical memory, in MB, available to processes running on the computer.

Interpretation

This counter only displays the last value and is not an average.
If the value is less than 20/25 percent of installed RAM it is an indication of insufficient memory.
Less than 100 MB is an indication that the system is very starved for memory and paging out.
Fluctuations of 100 MB or more can indicate that someone is logged in remotely into the server.
Pages/Sec

Definition

Pages/sec is the number of pages read from the disk or written to the disk to resolve memory references to pages that were not in memory at the time of the reference.

Interpretation

1 This is the sum of two counters - Pages Input/sec and Pages Output/sec.

2 The threshold is normally 20 pages/sec, although one has to investigate activity on the server before concluding paging is the problem.

3 Spikes in pages/sec are normal and possible due to backups, big files/data being written to disk and after reboot.

4 SQL Server has to be configured to dynamically manage to the "Dynamically configure SQL Server memory" option, and the "Maximum Memory" setting should be set to the maximum RAM possible with allowing room for OS. SQL Server should also ideally be the only application on the server.

5 High Available mbytes and low paging file % usage with high pages/sec may not indicate a problem, may merely be indicating that the system is reading a memory mapped file sequentially.

6 Also investigate Page Faults per second, which is the cumulative sum of hard and soft page faults since when the system rebooted. It may be hard to interpret this counter since it is a cumulative value and may be very large but if you have multiple programs sharing the computer with SQL Server you may be able to see which program is causing the paging by looking at each program’s page faults per second.

How to identify the SQL Server Start/Restart Date & Time ?

1. When was the SQL Server Service Started ?

SELECT agent_start_date FROM msdb.dbo.Syssessions WHERE session_id = (SELECT max(session_id) from msdb.dbo.Syssessions)

SELECT login_time from sys.dm_exec_sessions where session_id = 1

SELECT min(login_time) FROM Master..sysprocesses
-- SQL server 2008 only
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

SELECT login_time FROM Master..sysprocesses WHERE spid = 1

2. When was the TempDB database Re-Created ?
SELECT create_date 'SQL Server Started at' FROM sys.databases WHERE [name] ='tempdb'

GO