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
May 03, 2011
April 11, 2011
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.
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.
April 05, 2011
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.
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
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
March 25, 2011
Database mirroring
SQL server Database Mirroring
There are 3 types Database Mirroring Operating Modes: High Availability, High Protection and High Performance.
When database mirroring is used for high availability, if a principal server suddenly fails, if the mirror server has confirmation from the witness, it can automatically take on the role of principal and make its database available within a few seconds.But it need additional server called witness server.
In High Protection operating mode only a manual failover is possible, because there is no witness to fill the tie-breaker role. An automatic failover is not possible, because if the principal server fails, the mirror server has no witness server with which to form a quorum.
Client-side Redirect
In SQL Server 2005, if you connect to a database that is being mirrored with ADO.NET or the SQL Native Client, your application can take advantage of the drivers' ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string, and optionally the failover partner server.
For example, specifying server A as the principal, server B as the mirror, and AdventureWorks as the database name:
"Data Source=A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=True;"
The failover partner in the connection string is used as an alternate server name if the connection to the initial principal server fails. If the connection to the initial principal server succeeds, then the failover partner name will not be used, but the driver will store the failover partner name that it retrieves from the principal server on the client-side cache.If a client is successfully connected to the principal, and a database mirroring failover (automatic, manual, or forced) occurs. The next time the application attempts to use the connection, the ADO.NET or SQL Native Client driver will detect that the connection to the old principal has failed, and will automatically retry connecting to the new principal as specified in the failover partner name. If successful, and there is a new mirror server specified for the database mirroring session by the new principal, the driver will retrieve the new partner failover server name and place it in its client cache. If the client cannot connect to the alternate server, the driver will try each server alternately until the login timeout period is reached.
The great advantage of using the database mirroring support built into ADO.NET and the SQL Native Client driver is that we do not need to recode the application, or place special code in the application, to handle a database mirroring failover.
For additional information
http://technet.microsoft.com/en-us/library/cc917680.aspx
In SQL Server 2005, 2008 and 2008 R2 Database Mirroring works at the individual database level. One challenge to using database mirroring is to keep your important SQL Agent jobs enabled or disabled depending on a database's current mirroring role. If a database is acting as a Principal, you will want the SQL Agent jobs associated with that database to be enabled, while if it is acting as a Mirror, you will want to disable the SQL Agent jobs associated with that database. This does not happen automatically without a little bit of preparation.
http://blogs.msdn.com/b/sqlcat/archive/2010/04/01/using-sql-agent-job-categories-to-automate-sql-agent-job-enabling-with-database-mirroring.aspx
February 25, 2011
Move distribution database to another drive
Move distribution database to another drive:
There are scenarios in SQL server Replication that we need to move datafiles and logfiles of distribution database to another drive/folder.
Pl follow below steps to move it without having to redo the whole replication process.
1. Start and Stop a Replication Agent
To start and stop a Snapshot Agent or Log Reader Agent from Management Studio
Connect to the Publisher in Management Studio, and then expand the server node and the Replication folder.
Expand the Local Publications folder, and then right-click a publication.
Click View Snapshot Agent Status or View Log Reader Agent Status.
Click Start or Stop.
To start and stop a Queue Reader Agent from Management Studio
Connect to the Distributor in Management Studio, and then expand the server node.
Expand the SQL Server Agent folder, and then expand the Jobs folder.
Right-click the job for the agent, and then click Start Job or Stop Job. The name of the job for the Queue Reader Agent is in the form []..
To start and stop a Snapshot Agent, Log Reader Agent, or Queue Reader Agent from Replication Monitor
Expand a Publisher group in the left pane, expand a Publisher, and then click a publication.
Click the Agents tab.
Right-click an agent, and then click Start Agent or Stop Agent.
2. ALTER DATABASE distribution SET OFFLINE
3. Move the data and log file to the new location
ALTER DATABASE distribution MODIFY FILE ( NAME = distribution , FILENAME = 'C:\MSSQL\Data\distribution.MDF')
ALTER DATABASE distribution MODIFY FILE ( NAME = distribution_log , FILENAME = 'C:\mssql\Data2\distribution.ldf')
ALTER DATABASE distribution MODIFY FILE ( NAME = distribution_02 , FILENAME = 'C:\mssql\Data2\distribution_02.ndf')
4. ALTER DATABASE distribution SET ONLINE
5.Start the Log Reader Agent and Distribution Agent
There are scenarios in SQL server Replication that we need to move datafiles and logfiles of distribution database to another drive/folder.
Pl follow below steps to move it without having to redo the whole replication process.
1. Start and Stop a Replication Agent
To start and stop a Snapshot Agent or Log Reader Agent from Management Studio
Connect to the Publisher in Management Studio, and then expand the server node and the Replication folder.
Expand the Local Publications folder, and then right-click a publication.
Click View Snapshot Agent Status or View Log Reader Agent Status.
Click Start or Stop.
To start and stop a Queue Reader Agent from Management Studio
Connect to the Distributor in Management Studio, and then expand the server node.
Expand the SQL Server Agent folder, and then expand the Jobs folder.
Right-click the job for the agent, and then click Start Job or Stop Job. The name of the job for the Queue Reader Agent is in the form [
To start and stop a Snapshot Agent, Log Reader Agent, or Queue Reader Agent from Replication Monitor
Expand a Publisher group in the left pane, expand a Publisher, and then click a publication.
Click the Agents tab.
Right-click an agent, and then click Start Agent or Stop Agent.
2. ALTER DATABASE distribution SET OFFLINE
3. Move the data and log file to the new location
ALTER DATABASE distribution MODIFY FILE ( NAME = distribution , FILENAME = 'C:\MSSQL\Data\distribution.MDF')
ALTER DATABASE distribution MODIFY FILE ( NAME = distribution_log , FILENAME = 'C:\mssql\Data2\distribution.ldf')
ALTER DATABASE distribution MODIFY FILE ( NAME = distribution_02 , FILENAME = 'C:\mssql\Data2\distribution_02.ndf')
4. ALTER DATABASE distribution SET ONLINE
5.Start the Log Reader Agent and Distribution Agent
February 03, 2011
SQL server DBA Frequently used Checklists
1. how to check msdtc status on sql server 2005 server
i) Go to Services.msc and start Distributed Transaction Coordinator
ii)Go to Dcomcnfg.exe
My Computer -> Properties -> MSDTC Tab -> Security Configuration
Network DTC Access is checked
Network Administration is checked
Network Transactions is checked
Network Clients is checked
XA Transactions is checked
2.
i) Go to Services.msc and start Distributed Transaction Coordinator
ii)Go to Dcomcnfg.exe
My Computer -> Properties -> MSDTC Tab -> Security Configuration
Network DTC Access is checked
Network Administration is checked
Network Transactions is checked
Network Clients is checked
XA Transactions is checked
2.
Subscribe to:
Posts (Atom)
-
* Stored procedures generally result in improved performance because the database can optimize the data access plan used by the procedure an...
-
Why TempDB Grows and Doesn’t Reset Easily TempDB Usage Causes: TempDB is a global workspace for SQL Server, used for sorting, hashing, ...
-
Deadlocks are a side effect of blocking and not a SQL Server Bug. Poorly written queries in SQL Server can trigger deadlock in the system. W...
