Having SQL Server as a Database Solution to your enterprise applications, you may have a little chance of losing your valuable data, due to some failures, as SQL Server 2005 is having a lot of features for High-Availability and Disaster Recoverability.
One of such feature is LOG Shipping. Even though LOG Shipping is available from predecessors of SQL Server 2005, it is more robust with SQL Server 2005. In SQL Server 2000, logshipping is only available in Enterprise edition. In SQL 2005, it is available from Standard edition.
All you require a stand by server with a same SQL Server Configuration, and a tamper proof network connection between the production server and the standby server.
How LOGSHIPPING works….?
Before delving into LOGShipping, let us think over, how we were maintaining a stand by server for our failures?
Normally, we backup our Database in to a Device (either tape or a disk file) and move the backup to another server, and restore the backup there. In these situations, the down time of the Database will be more, depending upon the standby or warm server’s setup and how frequently we are doing this manualy. If the standby server is located near by the primary production server then the job will be done frequently. Suppose if the Standby Server is located in another part of the world, then what will happen!?
LOGShipping does all the steps described above in an automatic way. SQL Agent Service plays a vital role here.
A SQL Server Agent Job first takes the Backup of the Transaction log of the Database from the Primary Server (for which Logshipping is enabled) in a file and store it in a (specified) network shared path in a specified time interval. Then secondary server’s SQL Server Agent’s Job gets the files from the network shared path in the order that they were taken backup from primary Server Database and copy it in its file system and restore the transaction log to the standby server’s database. For this purpose, the network service account which is maintaining the SQL Server Agent Service in the Secondary server must have the Read Permission for the Network Shared Path.
Setting up a Logshipping in a Domain scenario , where the Primary Server and Standby Server are the members of the same Domain, it is advisable to use the same domain user account for the both Servers’ SQL Server Agent Service, so that no conflict will happen and give read /write permission for the network shared folder to that account. But in a no-domain scenario, to set up a log shipping, you have to adopt a tricky way. Create an user account with a same name and same password in both the Prinmary and the Secondary Server and make it member of SQL Server Agent Group and give full control over the Network Shared Path where Primary Server’s Database's Transaction Log Backup were stored.
Steps Involved in Logshipping.
1. Backup the primary database in to a device.
2. Restore the backup in the secondary Server with NORECOVERY option.
(Note: First time transport log manually. If your LOG file is too big in size, then, change the RECOVERY model of the DATABASE to Simple and take the Backup. It will reduce the size of your log. After taking the backup change the recovery model to FULL, because, you can set up logshipping for the Database with FULL recovery model. Following steps are handled by the scheduled jobs.)
3. Backup the Transaction Log of the Primary Database.
4. Copy the Transaction Log to Secondary Database.
5. Apply the Transaction Log To Secondary Database.
Steps to be followed in Failover
1. Backup the last transactions after the last schedule of LOGShipping from the Primary (fault) sever, if possible.
2. Apply to the secondary database.
3. Synchronize the user related to the database in the secondary server.
4. Reconfigure the server as Production Server.
PROS & CONS of LOGShipping
Pros are :
o Easy to implement.
o Easy to maintain.
o It is more reliable.
o Multiple standby servers can be configured.
o Stand by Server Database can be used for reporting purposed to minimizing the workloads of the primary server.
Here are some cons too…
o No automatic failover (The Database Mirroring, an another feature of SQL Server 2005, supports automatic failovers.)
o Manual failover requires a technocrat during failover.
o At least a minimum data loss depending upon how frequently your logs are being shipped.
Some last considerations….
All the steps in LOGShipping involves SQL Server Jobs, all that jobs are executed in a frequency of time, and the failover is manual (main drawback of LOGShipping) so keep the frequency of logshipping in a low time interval to minimize the amount of data loss during failover.
Give some special consideration for synchronizing the user logins in both servers (primary and secondary) for failover situations, so that you can switch the standby server as a production server to all of your client applications by simply changing the name or IP or both, and keep trouble-shooting the faulty server. If you succeeded in troubleshooting, you can make this server as standby server and started log shipping from the production server. Synchronizing users in secondary server may cause SID conflicts. So do it carefully.
If you have enough infrastructure, then it is recommended to have another server as Monitoring Server which involves the LOGShipping operation, and track status and statistics of LOGShipping.
After setting up LOGShipping, you can monitor the operations, through three tools available in SQL Server 2005.
1. TRANSACTION LOG SHIPPING STATUS REPORT - an built-in Report available in SSMS Standard reports.
2. SQL Server LOG files
3. SQL Server Agent’s Job History.
For further studies:
1. Read Chapter 27:LOGShipping and Database Mirroring , from the book ‘MICROSOFT SQL SERVER 2005 – Administrator’s Companion’ by Edward Whalen, Marcilina Gracia and others - MS PRESS.
2. Refer Books Online.
Friday, February 22, 2008
Friday, February 15, 2008
SQL Server 2005 Connectivity Problems
Last week, I have encountered a problem in connecting SQL Server 2005 from a remote machine, to a Server, in which Windows Vista Business Edition was the operating system.
After installing the SQL Server 2005 with SP2 in the Vista Business Machine, I started checking the SQL Server. It is working fine in local server. When I am trying to connect the same server from an Application (developed in VB.NET), I was unable to connect that.
My basic principle in diagnosing the problem is, always starting with zero. So I started ping the server IP and also the name of the Server. It was working fine.
Second I checked the Application, that the Server Name (and instance name in the correct format, ‘\’ should be used with instance name not the ‘/’) specified there is correct or not!? It was right!
Then I checked Server’s Surface area Configuration, whether the server is allowing remote connection or not! It is allowing! Then checked that the Protocols for Remote Connections are properly configured or not! That was also done! (As I knew the purpose of that Server earlier, after installing the SP2, I configured all these things earlier! Still I checked everything again!)
So everything is fine, and where lies the block!?
Then I started digging with error numbers in SQL Server Books Online and a lot of SQL Server related websites.
Most of the times I got the error was “Named Pipes Provider, error: 40 - Could not open a connection to SQL Server” . Best answer I have got on searching with this error is in SQLProtcols blogs. A must read blog for the every DBAs.( The last article published in that blog was July 2007)
Then I found that, SQL Browser Service was stopped and disabled! So I started with the following commands!( I typed in Command Prompt!)
sc config sqlbrowser start= auto
net start sqlbrowser
After starting the SQLBrowser, I was getting the Error (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
This error number is related to Firewall! Then I got that. I have to open the port ( the default port for my situation is the default port of SQL Server 1433) in firewall! The following link helped me! Step by step is explained there.
http://msdn2.microsoft.com/en-us/library/ms175043.aspx
Now it is working fine! Thank you MSDN!
After installing the SQL Server 2005 with SP2 in the Vista Business Machine, I started checking the SQL Server. It is working fine in local server. When I am trying to connect the same server from an Application (developed in VB.NET), I was unable to connect that.
My basic principle in diagnosing the problem is, always starting with zero. So I started ping the server IP and also the name of the Server. It was working fine.
Second I checked the Application, that the Server Name (and instance name in the correct format, ‘\’ should be used with instance name not the ‘/’) specified there is correct or not!? It was right!
Then I checked Server’s Surface area Configuration, whether the server is allowing remote connection or not! It is allowing! Then checked that the Protocols for Remote Connections are properly configured or not! That was also done! (As I knew the purpose of that Server earlier, after installing the SP2, I configured all these things earlier! Still I checked everything again!)
So everything is fine, and where lies the block!?
Then I started digging with error numbers in SQL Server Books Online and a lot of SQL Server related websites.
Most of the times I got the error was “Named Pipes Provider, error: 40 - Could not open a connection to SQL Server” . Best answer I have got on searching with this error is in SQLProtcols blogs. A must read blog for the every DBAs.( The last article published in that blog was July 2007)
Then I found that, SQL Browser Service was stopped and disabled! So I started with the following commands!( I typed in Command Prompt!)
sc config sqlbrowser start= auto
net start sqlbrowser
After starting the SQLBrowser, I was getting the Error (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
This error number is related to Firewall! Then I got that. I have to open the port ( the default port for my situation is the default port of SQL Server 1433) in firewall! The following link helped me! Step by step is explained there.
http://msdn2.microsoft.com/en-us/library/ms175043.aspx
Now it is working fine! Thank you MSDN!
Wednesday, January 30, 2008
.NET CLR Integration with SQL Server 2005.
Even though there are a lot of different thoughts in using CLR based codes inside SQL Server 2005, this feature of SQL SERVER provides a rich programming environment for both developers and DBAs. As the coding technology is in traditional way (with all of the language features) and integrating that code in a most secured area like SQL Server, this environment gives us to create safe, secure, scalable and feature - rich Stored Procedure, UDFs, UDTs, Triggers and User Defined Aggregates.
Prior to SQL Server 2005, the developers use their complex logics in COM Objects and call that COM objects with OLE Automation in SQL Server (using sp_OA*) Extended Stored Procedure. In SQL Server 2005, the error handling introduced in with TRY..CATCH block but it is still susceptible to untrappable errors, where we can handle these with structure error handling methods available in .NET languages. (http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQLCLRforDBAs.doc)
More advantages over using SQL CLR integration instead of XPROCs are
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
Once you configured your server to enable this feature, now your .NET code can be integrated with SQL Server.
Assemblies
Managed code is compiled and deployed in units called Assembly. If you create a package in .NET language it will be either .exe or .dll. SQL Server supports .DLL assemblies. First you have to register an assembly using CREATE ASSEMBLY in SQL Server before its functionality is used or processed.
Create Assembly TSQL Statement will register an assembly in SQL Server. Using WITH PERMISSION_SET keyword, you can specify security permission of the assembly. Permission set may be SAFE, EXTERNAL_ACCESS, or UNSAFE. Default permission set is SAFE.
Books Online is saying
“To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL Server, one of the following two conditions must be met:
The assembly is strong name signed or Authenticode signed with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate), and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies).
The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON. “
After an assembly is registered in SQL Server, It can be used.
To monitor and manage CLR objects, we can use CLR Catalog views , CLR related DMVs and DMFs.
Catalog Views
SYS.ASSEMBLIES Catalog view returns each row per assembly registered in SQL Server.
SYS.ASSEMBLY_FILES Catalog view return each row per file for all the files that makes up the assembly.
SYS.ASSEMLY_REFERENCES catalog view returns all each row for a pair of assemblies which is directly referencing other assembly.
Dynamic Management Views and Functions
sys.dm_clr_appdomains : Returns a row for each application domain in the server
sys.dm_clr_loaded_assemblies: Returns a row for each managed user assembly loaded into the server address space
sys.dm_clr_properties: Returns a row for each property related to SQL Server common language runtime (CLR) integration, including the version and state of the hosted CLR
sys.dm_clr_tasks : Returns a row for all common language runtime (CLR) tasks that are currently running
For Further Studies Read
Prior to SQL Server 2005, the developers use their complex logics in COM Objects and call that COM objects with OLE Automation in SQL Server (using sp_OA*) Extended Stored Procedure. In SQL Server 2005, the error handling introduced in with TRY..CATCH block but it is still susceptible to untrappable errors, where we can handle these with structure error handling methods available in .NET languages. (http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQLCLRforDBAs.doc)
More advantages over using SQL CLR integration instead of XPROCs are
- As CLR requests memory from SQL Server not directly from Windows, there is no managed user-code memory leaks making SQL Server slow or hanging up.
- As CLR and SQL Server integrated within, the CLR code runs within SQL Server also gaining safe and secure environment by SQL Security and .NET Framework environment’s security.
- This also makes safe SQL Server from user-code access violation cause crashes.
This CLR integration of Complex logics in Data layer itself reducing high cost of network traffic of high marshaling of data to COM Server (in case of Distributed COMs are in use)
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
Once you configured your server to enable this feature, now your .NET code can be integrated with SQL Server.
Assemblies
Managed code is compiled and deployed in units called Assembly. If you create a package in .NET language it will be either .exe or .dll. SQL Server supports .DLL assemblies. First you have to register an assembly using CREATE ASSEMBLY in SQL Server before its functionality is used or processed.
Create Assembly TSQL Statement will register an assembly in SQL Server. Using WITH PERMISSION_SET keyword, you can specify security permission of the assembly. Permission set may be SAFE, EXTERNAL_ACCESS, or UNSAFE. Default permission set is SAFE.
Books Online is saying
“To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL Server, one of the following two conditions must be met:
The assembly is strong name signed or Authenticode signed with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate), and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies).
The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON. “
After an assembly is registered in SQL Server, It can be used.
To monitor and manage CLR objects, we can use CLR Catalog views , CLR related DMVs and DMFs.
Catalog Views
SYS.ASSEMBLIES Catalog view returns each row per assembly registered in SQL Server.
SYS.ASSEMBLY_FILES Catalog view return each row per file for all the files that makes up the assembly.
SYS.ASSEMLY_REFERENCES catalog view returns all each row for a pair of assemblies which is directly referencing other assembly.
Dynamic Management Views and Functions
sys.dm_clr_appdomains : Returns a row for each application domain in the server
sys.dm_clr_loaded_assemblies: Returns a row for each managed user assembly loaded into the server address space
sys.dm_clr_properties: Returns a row for each property related to SQL Server common language runtime (CLR) integration, including the version and state of the hosted CLR
sys.dm_clr_tasks : Returns a row for all common language runtime (CLR) tasks that are currently running
For Further Studies Read
Monday, January 28, 2008
Database Snapshots
In SQL Server 2005, an interesting new feature is available is Database snapshots.(Of course it is available on Enterprise Edition only )
It is a point-in-time, read-only , Virtual copy of the source database that is a snapshot of a particular source database can be created in a particular time.It is most useful if your database contains historical data like Quarterly sales, year-wise employees performance. This snapshots can be used for further reporting purposes. Multiple snapshots can be created from single source database for different point of times.
When you create a snapshot for a Source database, SQL Server create an empty Sparse file(is NTFS File),If there are uncommitted transactions are there, it will not copied to Snapshot, the pages before the transactions are copied to Sparse file. NTFS Sparse file will not hold the user data at the time of creation.
Whenever the source database is modified after the snapshot is created, the Copy-On-Write operation is began for every snapshots! At the time of creation of a NTFS Sparse file, SQL Server creates a bitmap file for every sparse file, with bit for every page of the source database to check the page is copied to snapshot or not. When a page updating is in progress, SQL Server checks this bit and if the page is not copied then it will copy the page to snapshot(s). This is called Copy-On-Write operation. The Read from snapshot operation first checks this bit for whether read it from snapshot or from source database. This bitmap is stored in cache so it is always available until the SQL Server shuts down or the database is closed. If any one this two happen, then the bitmaps are need to be reconstructed when the database startups.
The snapshots can not be backed up an restored.More over if any snapshots are available for a particular database, that DB can not be dropped. Snapshots can not be attached or detached,
For security aspects, the all the security constraints are inherited to snapshots. If you drop a user from source database it will not dropped from snapshot!
Need more about Database snapshots
It is a point-in-time, read-only , Virtual copy of the source database that is a snapshot of a particular source database can be created in a particular time.It is most useful if your database contains historical data like Quarterly sales, year-wise employees performance. This snapshots can be used for further reporting purposes. Multiple snapshots can be created from single source database for different point of times.
When you create a snapshot for a Source database, SQL Server create an empty Sparse file(is NTFS File),If there are uncommitted transactions are there, it will not copied to Snapshot, the pages before the transactions are copied to Sparse file. NTFS Sparse file will not hold the user data at the time of creation.
Whenever the source database is modified after the snapshot is created, the Copy-On-Write operation is began for every snapshots! At the time of creation of a NTFS Sparse file, SQL Server creates a bitmap file for every sparse file, with bit for every page of the source database to check the page is copied to snapshot or not. When a page updating is in progress, SQL Server checks this bit and if the page is not copied then it will copy the page to snapshot(s). This is called Copy-On-Write operation. The Read from snapshot operation first checks this bit for whether read it from snapshot or from source database. This bitmap is stored in cache so it is always available until the SQL Server shuts down or the database is closed. If any one this two happen, then the bitmaps are need to be reconstructed when the database startups.
The snapshots can not be backed up an restored.More over if any snapshots are available for a particular database, that DB can not be dropped. Snapshots can not be attached or detached,
For security aspects, the all the security constraints are inherited to snapshots. If you drop a user from source database it will not dropped from snapshot!
Need more about Database snapshots
- Chapter 4. Databases and Database Files from the book Inside Microsoft SQL
Server 2005 : The Storage Engine by Kalen Delaney.- Read More
about Read / Write operations in snapshots.- For
further pros and cons of database snapshots read this.- Chapter 4. Databases and Database Files from the book Inside Microsoft SQL
Monday, January 7, 2008
SET NOCOUNT ON – A simple Performance Booster
How many of us using the SET NOCOUNT ON statement in our stored procedures? This simple one line code do a lot of differences in performance (in terms of Network Traffic which is one the performance related issue) of SQL Server.
What will this do is simply cut off the number of rows affected by the stored procedures’ Select / Insert / Delete/ Update statements. According to BOOKS ONLINE, When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.
By default value of SET NOCOUNT is OFF.
If your application really wants the number of rows affected, then you can use @@RowCount variable which is very handy.
On reading this BOL, a question is raised on my mind, that what happened if I set this on, to SQLDATAREADER’s RecordsAffected Property ? Is there any link between this SET NCOUNT? But it is not having any link. Because the remark section of the SqlDataReader.::.RecordsAffected Property in MSDN (http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.recordsaffected.aspx) is saying that The RecordsAffected property is not set until all rows are read and you close the SqlDataReader.
So to reduce network traffic, you can add this simple line at the starting of every stored procedure. For further digging read the followings.
http://www.sqlmag.com/Article/ArticleID/22093/sql_server_22093.html
http://weblogs.asp.net/jgalloway/archive/2006/08/30/How-NOCOUNT-affects-ADO.NET.aspx
Subscribe to:
Posts (Atom)