Error 1418, fails to connect sql 2008 mirror server:

• The server network address can not be reached
• Alter failed
• Error 1418
• Insufficient Log Data
• Error 1478

Start with: In this example we are inside a DOMAIN. We have a domain user called khan_machine

1)      Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2)      khan_machine is local admin in both machines. Also is a sysadmin user in both sqlservers.

3)      khan_machine  is the MSSQLSERVER service account in both machines

4)      Machine A is gonna be the PRINCIPAL, B the MIRROR.

5)      Log on in my machine as khan_machine

6)      Open Sql server Studio. Connect to both machines using windows authenticacion.

7)      Create a New Database in A (you may need to have sysadmin role to khan_machine using sa) create a table, add some data

8)       Backup the new DB (Full backup) with .bak extension

9)       Backup the new DB (Transaction log) with .trn extension

10)    Copy the both files to a location in B machine.

11)     Restore .bak into a new DB using NORECOVERY option

12)     Restore .trn into the previous DB using NORECOVERY option

13)    Go to Machine A, open a new query

14)    Take a look to the endpoints

Run this syntax in QA:

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15)   Delete existing ones (DROP ENDPOINT [NAME])
16)   Create a new endopoint

Run this syntax in QA:
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17)   Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18)   Do steps 13 to 17 in machine B.

19)   Return to machine A. Query.

20)   Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER =’TCP://MACHINENAME:5023′

21)   Repeat step 20 from MACHINE B pointing to MACHINE A

22)   Note: Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23)   Lets test them: Add some new data in Machine A database.

24)   Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25)   Refresh the 2 databases in the UI you should see the new status and roles changed.

26)   Open Machine B databse, the new data should be there.

To establish any mirroring session (with or without certificates) you must use FQDN (Fully Qualified Domain Names) machine name.domain.com.  ** Before going on, be sure that you can reach the 3 machines with a single PING. Also, is a good practice to ensure that you can reach a telnet call over the FQDN + the port you plan to use (5022 in this example)** 

FIX: Over the post above. When establishing a mirroring session using machine accounts (not certificates, so you are inside a DOMAIN)  ensure your 3 mssqlservices are running under NETWORK SERVICE account

För more information have a look here

Khan – MCTS

Microsoft SQL Server 2008 Service Pack 2 download here 

Microsoft SQL Server 2008 Service Pack 2 (SP2) is now available for download.

Top Tips for Effective Database Maintenance

Paul S. Randal

Several times a week I’m asked for advice on how to effectively maintain a production database. Sometimes the questions come from DBAs who are implementing new solutions and want help.  It’s worth to go through here is link: http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

Some other topic which you might want to know:

Twit List
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69568

It’s dead, Jim
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67782

Not Even Stupid
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79305

 Khan – MCTS

Log Shipping is one of the solutions for High Availability for business critical application. Log Shipping is first introduced in SQL Server 7.0.  Log Shipping; as name defines does the shipping of Transaction Log to another server; it does exact replica of the database; it does backup of Transaction Log and restores it on a Secondary /Stand by Server which keeps database on secondary server in sync with the database on Primary Server. In case of failure work loss happens only for the time being copy of T-Log backup and restoring back to
Secondary/Stand by Server. Stand by / Secondary server which can act as a primary server in case of failure.

SQL Server DBA has to perform some manual tasks to bring it online for business. There are other options like Clustering and Replication for High Availability of Business Critical Application, so why do we go for Log Shipping? that good question so here we go .. 

Well, there are reasons for choosing Log Shipping against Cluster and Replications: Its easy to maintian and cost$ is less then other technolog, such as cluster, some details why we go for LS.

1. Setting up: It is very easy to create a Log Shipping, only thing you need to identify is you must have two servers with SQL Server Enterprise Edition and a database(s) which is critical to your business environment.
2. Manageability: It is easy to manage Log Shipping comparing to Replication and Cluster Server, generally Log Shipping works well and you don’t feel panic with its monitoring or maintenance work.
3. Act as a Reporting Server: Stand by or Secondary Server can act as a Reporting Server in your organization, your database which is being log shipped can be used for Read Only queries or I would rather say it can act as a Reporting Server. The thing need to be consider here is it will be unavailable at the time of Restoration of Transaction Log e.g. if your Transaction Log backup takes 10 minutes of Restoration Time and you does Log Shipping every 60 minutes then your Reporting/Secondary/Stand by Server will be un-available for 10 minutes.
4. Can use multiple database(s)/server(s): You can use multiple server(s) as a Stand by or Secondary Server for different purpose, e.g. you can create two Stand by server(s) one for Reporting and another for High Availability
5. Cheaper then cluster: If you go for a Cluster Server you will have to meet software and Hardware requirement; I mean you need identical hardware to setup clustering where as in case of Log Shipping you don’t need to have exactly the same set of hardware for Primary and Secondary or Stand by server.
6. Best solution for physical dispersed location: Data Center: In your environment you are having a cluster server configured at your Data Center for high availability so that if in case of any failure or damaged you can recover your data back, imagine what if your data center itself got damaged!!!!!! In this type of scenario Log Shipping does good job you can configure Log Shipping to Dr Server which is at different physically location and if your local Data Center / Server got damaged you may still recover your data. You can do this thing using Replication too; in case of replication again you have to select Snapshot/Transactional/Snapshot Replication, now as you may use Log Shipping you would rather go for Snapshot Replication, you have to monitoring it regularly whether it is running or not!!! Where as with Log Shipping it generally runs smoothly and even if it caught an error it will tell you exact where it breaks so fixing a Log Shipping is not difficult.

Prerequisite
1. Database must be in Full or Bulk-Logged Recovery Model.It is required that Database has to be in Full or Bulk-Logged Recovery Model,One can perform T-Log backup only when Database Recovery Model is set to Full or Bulk-Logged, here is a brief on recovery model.

3. Create a share on Primary Server and Secondary Server with change and write permissions for Windows Account User which is used for SQL Server Agent.

4. You may Restore Full Database on Secondary Server manually or it can be done via Log Shipping installation itself at the time of setup process (Ensure this database on Secondary Server must be restored with NORECOVERY or STANDBY option).It is required to Restore Database with NORECOVERT STANDBY because if the database restored in recovered/write enabled mode you can not apply another T-Log.

Optional Component : Monitoring / Witness Server
Identify your Monitor Server which will look after for your Primary Server and SECONDARY Server’s Activity.It is better to use separate server which monitors the activity of Primary and Secondary Server so that you can get the alert even if server itself won’t works.

Primary Server:
1. Create a Share with read/write permission for domain user which we’ll be using to startup SQLServerAgent
2. Create a Full Database backup for LogShDB
3. Backed up T-Log on the folder you have created earlier to store the log backup You can create a maintenance plan to take a T-Log backup for regular interval and then add it as a Step in job to automate this process
4. Please ensure that the duration must enough to copy the log backup file to Secondary Server.Suppose we are taking T-Log backup each 1/2 hour and if the backup file is big enough which takes more then 1/2 to copy over the network share, it will again creates a new T-Log backup which will then occupies recourse unnecessarily.
5. Copy T-Log backup file to Secondary Server’s shared folder
6. Delete the T-Log backup file after it copied to the Secondary Server’s shared folder.
7. Ensure you have selected Allow Database to assume primary role option so that if in future requirement of role reversal can be possible easily.If in case we need to change the role of primary server or secondary server to act as Primary or Secondary this option should be used while setting up Log Shipping.
8. Ensure that you have set up proper threshold values for Destination Database for Out of Sync, Load Time Delay, File Retention Period and History Retention Period option.These options are self explanatory: Out of Sync=used by witness server to send alert if the specified time has been exceeded between Last T-Log backup on primary and secondary server, Load Time Delay= Destination Database waits before it restores the T-Log, File Retention Period= Time elapse till file gets deleted and History Retention Period= Time till we retain History details
Secondary Server
1. Check if the Database is already exists on Secondary Server, if it is already their drop it.
2. Restore a Full Database Backup for LogShipping database. Restore a T-Log for LogShipping database. on the secondary server which is copied on the shared folder of Secondary Server;
4. Ensure you have given STANDBY or NORECOVERY option while restoring T-Log backup for LogShipping database..

You may find log shipping is a very use full yet simple to configure, use and manage, it is cheaper and easily manageable solution for High Availability of your data(Most of the company want cheep and good solution for there invoriment). It is a good solution over clustering (because it is costly; requires identical hardware to setup cluster) and easily manageable, easy troubleshooting against replication as it clearly says where it breaks.

Generally Log Shipping works very smooth once it is configured. Even if you have to re-configure it from the scratch it is easy job to do, which i love that.