Mirror Setup

Hello,

I'm trying to setup database mirroring between 3 servers and the details are as follows:

1. All 3 machines reside in the same domain.

2. Server1 consists of SQL 2005 Ent Edition without SSAS, Server 2 consists of SQL 2005 Ent Ed without SSAS & SSRS, Server 3 consists of SQL 2005 Express Ed.

3. All the 3 machines are having their individual service domain accounts.

4. Server 1 consists of 5 databases which needs to be mirrored to Server 2. I took full backups and tlog backups and restored the full backups in NO Recovery mode on Server 2 and then applied the tlog backups to sync them with Server 1.

5. I created the logins on Server 1 (Server 2 & 3), Server 2 (Server 1 & 3) and Server 3 (Server 1 & 2) and gave "sa" permissions respectively.

6. I kept the first database on Server 1 to full recovery mode, created an endpoint with the default port and granted connection to Server 2 & 3 logins.

7. I created an endpoint on Server 2 with the default port number and granted connection to Server 1 & 3 logins.

8. I created an endpoint on Server 3 with the default port number and granted connection to Server 1 & 2 logins.

9. On Server 2 I'm executing this statement

ALTER DATABASE <dbname>

SET PARTNER = 'TCP://<Server Network Address of Server 1>:<Port No>'

10. On Server 1 I'm executing this statement

ALTER DATABASE <dbname>

SET PARTNER = 'TCP://<Server Network Address of Server 2>:<Port No>'

ALTER DATABASE <dbname>

SET WITNESS = 'TCP://<Server Network Address of Server 3>:<Port No>'

On Step 9 & 10, I'm getting an error message "Msg 1416, Severity 16, State 2 Database <dbname> is not configured for mirroring" in the query window but when I see the error logs individually on Server 1 & 2 I'm seeing "Error: 1443, Severity: 16, State: 2. Database mirroring has been terminated for database 'dbname'. This is an informational message only. No user action is required."

I would appreciate if any one can look into the problem I'm facing. I've googled and could not come up with a solution.

Thanks

I just have one question, did you restore the tran logs from principal to mirror using with norecovery option?.......coz you need to restore atleast 1tran log in mirror with norecovery option prior to configuring mirroring......|||

Hello Deepak,

I have restored 2 sets of tlog backups from principal to mirror -- first with norecovery mode and the second one with recovery mode. Still I'm getting the same errors.

Query Window

===========

Msg 1416, Level 16, State 31, Line 3

Database " " is not configured for database mirroring.

Error Log

=======

Error: 1443, Severity: 16, State: 2.

Database mirroring has been terminated for database ' '. This is an informational message only. No user action is required.

Do you have any other workaround for this problem?

Thanks

|||

Hello Deepak,

I have even tried with a sample database on the same set of servers and encountering the same set of errors in query window and error log. Is it something to do with any sort of configuration which I need to do before implementing database mirroring?

Thanks

|||

here is the mistake,

1. you should restore the full backup in the mirror using with norecovery clause

2.you need to restore one tran log in mirror using with norecovery clause

the idea behind restoring the full backups and tran log backups using with norecovery is that you can apply further transaction logs

since you have applied one with norecovey and other with recovery option further tran logs cannot be applied in the mirror...that seems to be the error.........perform the 2 steps as above and then start mirroring.....

|||

Hello Deepak,

I took 1 full backup and 2 tlog backups from the principal server.

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode

These are the steps I have initiated to configure database mirroring. I have even tested with a sample database following the above steps and still encounter the same error messages.

Thanks

|||

abybody out there who can troubleshoot my problem?

TIA

|||

well as i said earlier you need to restore the full and subsequent tran log backups in mirror using with norecovery option.

this step has to be performed prior to configuring mirroring....further adding to your problem the error says, that the "database is not configured for mirroring".....which is a clear indication that either your mirror is not restored with norecovery or it is not in Full Recovery i believe.....this is what i know.....

|||

Hello Deepak,

It seems you haven't gone through my earlier messages and here it is

I took 1 full backup and 2 tlog backups from the principal server.

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode

These are the steps I have initiated to configure database mirroring. I have even tested with a sample database following the above steps and still encounter the same error messages.

Thanks

|||

refer this,

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode > this has to be in norecovery mode but u have restored it in recover mode other steps are fine

|||

Hello Deepak,

Thanks for pointing the problem and I could setup mirroring successfully..

Mining Model predicts same set of products for all cases

Hi

I have developed a product

basket mining model as follows

DSV

SELECT C.CustomerId,C.CustomerIdName,P.ProductId,P.ProductIdName

FROM Customer INNER

JOIN CustomerProduct

ON C.OpportunityId

= P.OpportunityId

Mining Structure

CREATE MINING MODEL ProductBasket

(

CustomerId

TEXT KEY,

CustomerIdName

PREDICT,

ProductId

PREDICT,

ProductRecommend

TABLE PREDICT

(

ProductId TEXT KEY

ProductIdName PREDICTONLY

)

)

USING Microsoft_Association_Rules

Prediction Query

Since I want the output in the following format

Product

ID

Items (nested Table)

Product

A

product B

Product C

Product

B

Product A

Product C

I have written the prediction query as follows

SELECT

t.[ProductId],

PredictAssociation([Association].[ ProductId],3)

From

[Association]

PREDICTION JOIN

OPENQUERY([Adventure Works Cycle MSCRM],

'SELECT DISTINCT

[ProductId]

FROM

(SELECT ProductId FROM ProductBase)

as [Product]

') AS t

ON

[Association].[Product Id] = t.[ProductId]

The model is predicting the

same set of products for every case. Even changes in the algorithm parameter

value do not have any impact on the result.

What is the reason for this and

how can u rectify it?

If you're doing a simple recommendation model (i.e. a model that provides recommendations based on what's in the user's shopping basket, there is no need to have the product id at both case and nested level. Your modeling seems unneccessarily complex for this scenario - it also explains why you're seeing the same recommendations for all cases: based on the DSV you've listed, your training cases don't include the basket contents at all for each transaction.

If you use a model definition similar to the one I described in an earlier thread on this topic (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=649759&SiteID=1), your query would look like this:

SELECT t.CustomerID, PredictAssociation(AssociationModel.Products, 3) FROM PM
PREDICTION JOIN
SHAPE {
OPENQUERY(ds, 'SELECT CustomerID FROM CustomerProducts ORDER BY CustomerID')
}
APPEND ( {
OPENQUERY(ds, 'SELECT CustomerID, ProductName FROM CustomerProducts ORDER BY CustomerID') }
RELATE CustomerID to CustomerID ) AS Products as t
ON AssociationModel.Products.ProductName = t.Products.ProductName

The core idea is that the input case (for prediction as well as training) is the set of products in the customer's basket and the prediction is the list of recommended products. Both the input and prediction map to the same nested table in the mining structure/model.

|||

The first query should be SELECT DISTINCT CustomerID ...

|||

Also, if you want simply a list of recommendations per product, and you first created a model based on customer baskets you could then create a query like this to get the complete list of product recommendations:

SELECT t.ProductName, PredictAssociation(Products, 3) FROM PM
PREDICTION JOIN
SHAPE {
OPENQUERY(ds, 'SELECT DISTINCT ProductName FROM CustomerProducts ORDER BY ProductName')
}
APPEND ( {
OPENQUERY(ds, 'SELECT DISTINCT ProductName FROM CustomerProducts ORDER BY ProductName') }
RELATE ProductName to ProductName) AS Products as t
ON PM.Products.ProductName = t.Products.ProductName

This works because you are specifying a basket containing a single item as input for the prediction on a model that was created on customer baskets. Using the productname on the top level of the input query for the prediction is just a shortcut since we know we want baskets of size 1 anyway. Doing an input query like this for the model training would cause no rules to be found since only baskets greater than size 1 imply any patterns.