SQL Server 2005 Log Shipping

Log Shipping
As I have tried a lot of combinations in to find out an effective method for implementing Transaction Log Shipping between servers.

Here's the steps that I finally found that works. Let's assume there are 2 servers with SQL Server 2005. Make sure both servers have latest SP.

In this example I am going to explain the log shipping between two servers, connected through vpn, server A location Europe and server B location USA.

1. Make sure you have created same user accounts with full permissions on both servers, if you are the DBA sa is preferred.

2. Make sure File Sharing is enabled on the local area connection between the server. Also enable file sharing in Firewall.

3. Make sure the local network connection is not regular LAN. Recommended be a gigabit card with near zero data corruption. Both cable and switch needs to be perfect. If possible, connect both servers using Fibre optic cable directly on the NIC in order to avoid a separate Switch.

4. Now create a folder named sqldata on both servers. Let's assume the folder is on E:\Transfer.

5. On Primary Database server, share the folder sqldata and allow windows account Full Access to it. Then allow windows account FullAccess on TranLogs folder. So you are setting the same permission from both "Sharing" tab and from "Security" tab.

6. On Secondary database server, allow windows account Full Access right on Transfer folder. No need to share it.

7. Test whether windows Account ( in our case sa account) can really connect between the servers.

8. The sql account needs to be set up with full access permission. In the example below syncaccount is given as an example.





































9. Now connect the secondary server in the primary server in the ssms and make sure the connectivity is working fine.

10. Now backup the database from the primary database say test.bak and the transaction log back up as test.trn.

11. Copy the file the test.back and the test.trn to the secondary server and restore the database test on the secondary database. please see image below




















































































12. Once you restore the test database in the secondary server the database will be in read only mode, but the advantage is this database can be accesses through sql query analyzer, where as in mirroring this option is not available.

13. Now let us come back to the primary server, right click on the test database and click properties --- transaction log shipping.

14. Click on the check box Enable this as a primary database in a log shipping configuration.




15. Click on back up settings and specify the back up of the transaction log files to be backup, please note this can be a network folder or you local folder. So if you need to do on the local folder say for example f:\vijay\ .... as shown in the example below.







16. Now we have the transaction log files copies on this location.



17. Next step is to add the secondary server, click on add, connect to the secondary server, as we have already set up the test database on the secondary server click on No the secondary database is initialized.

18. Now click on the copy files tab and enter the location of the secondary server file location where the transactin log files will be copied. Please note the user should have full access to this location with read, write permissions.

19 . Next step is to click on the Restore Transaction Log tab and check the Standby Mode and check the Disconnet the users in the database when restoring backups.

20. Now go back to the secondary server and check if the following sql jobs are created and they are running fine. I have xxx just to remove by company name.

LSRestore_xxx-HOST\GOES_test
LSCopy_xxx-HOST\GOES_test


I hope this helps in setting up the sql server 2005 log shipping.

Moving SQL Database Files mdf and ldf

--check the location of the files

Use databasename

GoExec sp_helpfile

--detach the database name
Exec sp_detach_db 'databasename'

--copy the mdf and ldf files to the location

--attach the databasename

Exec sp_attach_db 'databasename',

'D:\sqldata\GPSBSBBVDat.mdf',
'G:\sqldata\GPSBSBBVLog.ldf'

SQL Having clause....

--Having clause

select name,sum(dpgrossbookingpriceineuro) from vtgeneral
where bookdate >= '2008-07-01' and bookdate <= '2009-06-30'
group by name
having sum(dpgrossbookingpriceineuro) > 50000
order by sum(dpgrossbookingpriceineuro) desc

select name,sum(dpgrossbookingpriceineuro) from vtgeneral
where bookdate >= '2008-07-01' and bookdate <= '2009-06-30'
group by name
having sum(dpgrossbookingpriceineuro) < 5000
order by sum(dpgrossbookingpriceineuro) desc

SQL Performance and Optimization

The sql server performance and optimization can be improved in many ways, but it again depends on the number of factors and individual scenariso, here are some the of steps taken by myself.

1. Check the size of the tables, you can use the query from my post which identifies the largest table.

2. Check the max. cpu utilization, you can use the query from my post on this. Then you can work on the sql query.

3. Check the indexes on the table, see if the tables have clustered index and non clustered index, it depends on how the table is queried and what fields are queried.

4.Location of the mdf and ldf files, it is advisable for optimimum performance to locate the temp database file on d:/ drive, mdf on the e:/ and the ldf on the f:/ and leave the operating system on the c:/.

5. Check the views on the database this may used in excess, too many views causes concern on the performance of the database and on the sql server.

Identify the No of days between job status

The above table represents the status of a jobnumber from order entery to completion. In this case for performance mprovement if we need to identify the number of days it has taken between each status

drop table #temp
delete from tbltulsaopkpi
create table #temp( id int identity , lotno varchar(30), jobDate datetime, status varchar(5) )
insert into #temp
select jobnumber, date,jobstatus
from tbltransjobstatus where jobnumber = 'A8000160-1'
--select * from #temp
declare @ctr int, @jobstatus varchar (3), @lotno varchar(20)
SELECT @ctr = count(id) FROM #temp
declare @olddate datetime, @newDate datetime
declare @i as int, @noDays as int
set @i= 1
set @noDays = 0
WHILE @i <= @ctr -- - 1 >= 0
BEGIN
if @i = 1 --or @i =@ctr
BEGIN
select @oldDate = jobdate, @lotNo = lotno, @jobStatus = status
from #temp
where id = @i
-- select @oldDate, @lotNo, @jobStatus, 0
insert into tbltulsaopkpi values
(@lotno,@jobstatus,0)
END
else
BEGIN
select @newDate = jobdate, @lotNo = lotno, @jobStatus = status
from #temp
where id = @i
set @noDays = datediff(day,@oldDate, @newDate)
- (datediff(ww,@oldDate, @newDate + case when datename(dw,@oldDate) = 'sunday' then 1 else 0 end))
- (datediff(ww,@oldDate, @newDate + case when datename(dw,@newDate) = 'saturday' then 1 else 0 end))
- (datediff(ww,@oldDate, @newDate + case when @newDate not in (select date from tblholiday where finyr = dbo.ufgetfinyr(@newDate)) then 1 else 0 end))
insert into tbltulsaopkpi values
(@lotno,@jobstatus,@noDays)
-- select @newDate, @lotNo, @jobStatus, @noDays
set @oldDate = @newDate
END
set @i = @i + 1
-- create table tbltulsaopkpi (
-- lotno varchar(20),
-- jobstatus varchar(5),
-- NoofDays int
-- )
END
-- select * from tbltulsaopkpi

--writter by marquerite

SQL Restore from Backups

Please note to have this working the BACKUP has to be done first, then from that point the transaction log has to be done. If there a backup done in between this process the RESTORE may not work properly.


Sql script to RESTORE DATABASE, please note this can also be done through wizards in SSMS.

RESTORE DATABASE [test]
FROM DISK = 'E:\transfer\vijay\test_backup_201001111451.bak'
WITH FILE = 1, NORECOVERY,
MOVE N'test' TO N'E:\MSSQL\test.mdf',
MOVE N'test_Log' TO N'F:\MSSQL\test_log.ldf',
NOUNLOAD, STATS = 10

Sql script to RESTORE TRANSACTION LOG files, this is applicable when you do the full back up and transaction log back ups. This option enables to get to the point of data loss.

RESTORE LOG [test]
FROM DISK = 'E:\Transfer\vijay\test.trn'
WITH FILE = 1,
RECOVERY

SQL Backup

Sql backup

You can do a complete full backup on daily basis in the night and subsequently do a transaction log backup for every hour or every two hours

The next option is to do a set the options to simple and do a simple backup daily then do a transaction back up every two hours.



-*********************************************************************************
--BACKUP DATABASE
--*********************************************************************************
BACKUP DATABASE [Pacop] TO
DISK = N'Z:\DATABASE\Pacop_backup.bak' WITH NOFORMAT,
INIT, NAME = N'Pacop_backup', SKIP, REWIND, NOUNLOAD,
STATS = 10
--*********************************************************************************
--BACKUP LOG OVERWRITE THE TRN LOG FILE
--*********************************************************************************
BACKUP LOG [Pacop] TO DISK = N'Z:\DATABASE\TRANSACTION LOG BACKUPS\Pacop.trn'
WITH NOFORMAT, INIT, NAME = N'Pacop-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
go
DBCC SHRINKFILE (N'Pacop_Log' , 786)
--*********************************************************************************

--*********************************************************************************
--BACKUP LOG APPEND THE TRN LOG FILE
--*********************************************************************************
BACKUP LOG [Pacop] TO DISK = N'Z:\DATABASE\TRANSACTION LOG BACKUPS\Pacop.trn'
WITH NOFORMAT, NOINIT, NAME = N'Pacop-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

--*********************************************************************************
--*********************************************************************************
DBCC SHRINKFILE (N'Pacop_Log' , 786)
--*********************************************************************************