Mirroring between Standard and Enterprise editions...

Hi All,

We are going to use database mirroring and database snapshots for reporting. The principal server runs SQL Server 2005 Standard Edition and mirror (reporting) runs SQL Server 2005 Enterprise Edition (64-bit). I failed to setup database mirroring trough GUI using SQL Server Management Studio and got this error:

This mirroring configuration is not supported. Because the principal server instance, <server_name>, is Standard Edition, the mirror server instance must also be Standard Edition.

But when I use T-SQL commands I was able to setup database mirroring and it works OK so far.

SQL BOL says that one of requirements for database mirroring is to use the same SQL Server editions but I am wondering if it works trough T-SQL why MS mention it in the requirements list, also is totally unsupported by MS?

Thanks

Oleg

Oleg,

While this will probably work fine, we don't test this combination internally. If you mirror from a principal that is STD edition to a mirror that is EE edition, it would probably work without issues. Mirroring the other way (from EE to STD) could be problematic because of the EE features that are not present in STD. Since mirroring supports failover and failback, in theory it is possible for you to be in the situation where you are mirroring from EE->STD edition even if that is not the initial configuarion.

Regards,

Matt Hollingsworth

Program Manager, SQL Server High Availability

|||

Matt,

Thanks for your answer.

Our plan is to use database mirroring only for reporting and not for high availability. (We use replication and remote DC for HA). So mirroring from Standard to Enterprise Edition will be the only one scenario that we going to use option in our reporting environment.
The concern that we have is if we will be able to use mirroring between Standard and Enterprise in the future releases of SP2, SP3.

Thanks Oleg

|||

I am curious to why you want EE on the mirror server?

Only because the database snapshot feature?

Actually this is very similiar to what we need (use mirroring mostly for reports, not for HA) but since we don't have EE, we actually setup 2 STD instances on the mirror server

Server1 <- live server

Server2\Mirror <- mirrored, can be automatic fail over

Server2\Log <- log shipping, used for reports

Server3 <- witness and log shipping monitor server

Although I do recall seeing someone mention that we do not need a license if only using the mirror server in standby mode

Does that mean we can use EE with snapshot on Server2 without having to pay a license?

Thanks

|||

Yes we are going to use EE edition only for reporting since it doesn't require extra support efforts and also we can have a fair data refresh latency.
For the stand-by server you don't have to pay for licensing and it's valid for cluster or mirroring unless you also going to use it also for reporting.

tnx.

I'm closing this topic, thanks to all for answers and inputs............OH

|||

>> Although I do recall seeing someone mention that we do not need a license if only using the mirror server in standby mode

No, it doesn't. This only applies if you are using the 2nd server for failover only. By creating snapshots and running reporting off of it, it is no longer a failover only server.

Also, in order to cover both servers under 1 license, the license has to be valid for each server. So, you would have to run both under an Enterprise license, not a standard license.

Mirroring between Standard and Enterprise editions...

Hi All,

We are going to use database mirroring and database snapshots for reporting. The principal server runs SQL Server 2005 Standard Edition and mirror (reporting) runs SQL Server 2005 Enterprise Edition (64-bit). I failed to setup database mirroring trough GUI using SQL Server Management Studio and got this error:

This mirroring configuration is not supported. Because the principal server instance, <server_name>, is Standard Edition, the mirror server instance must also be Standard Edition.

But when I use T-SQL commands I was able to setup database mirroring and it works OK so far.

SQL BOL says that one of requirements for database mirroring is to use the same SQL Server editions but I am wondering if it works trough T-SQL why MS mention it in the requirements list, also is totally unsupported by MS?

Thanks

Oleg

Oleg,

While this will probably work fine, we don't test this combination internally. If you mirror from a principal that is STD edition to a mirror that is EE edition, it would probably work without issues. Mirroring the other way (from EE to STD) could be problematic because of the EE features that are not present in STD. Since mirroring supports failover and failback, in theory it is possible for you to be in the situation where you are mirroring from EE->STD edition even if that is not the initial configuarion.

Regards,

Matt Hollingsworth

Program Manager, SQL Server High Availability

|||

Matt,

Thanks for your answer.

Our plan is to use database mirroring only for reporting and not for high availability. (We use replication and remote DC for HA). So mirroring from Standard to Enterprise Edition will be the only one scenario that we going to use option in our reporting environment.
The concern that we have is if we will be able to use mirroring between Standard and Enterprise in the future releases of SP2, SP3.

Thanks Oleg

|||

I am curious to why you want EE on the mirror server?

Only because the database snapshot feature?

Actually this is very similiar to what we need (use mirroring mostly for reports, not for HA) but since we don't have EE, we actually setup 2 STD instances on the mirror server

Server1 <- live server

Server2\Mirror <- mirrored, can be automatic fail over

Server2\Log <- log shipping, used for reports

Server3 <- witness and log shipping monitor server

Although I do recall seeing someone mention that we do not need a license if only using the mirror server in standby mode

Does that mean we can use EE with snapshot on Server2 without having to pay a license?

Thanks

|||

Yes we are going to use EE edition only for reporting since it doesn't require extra support efforts and also we can have a fair data refresh latency.
For the stand-by server you don't have to pay for licensing and it's valid for cluster or mirroring unless you also going to use it also for reporting.

tnx.

I'm closing this topic, thanks to all for answers and inputs............OH

|||

>> Although I do recall seeing someone mention that we do not need a license if only using the mirror server in standby mode

No, it doesn't. This only applies if you are using the 2nd server for failover only. By creating snapshots and running reporting off of it, it is no longer a failover only server.

Also, in order to cover both servers under 1 license, the license has to be valid for each server. So, you would have to run both under an Enterprise license, not a standard license.

Minor Table Insert help

i have the following code, it all works how i want it to bar the first time it runs, when i run the program and insert the data the first time, it inserts the data twice, all other times only once or the update.

$dbh=mysql_connect ("localhost", "twqwwsoy_user", "iiyama") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("twqwwsoy_resources");

$count="SELECT COUNT(message) FROM Diary";
$result = mysql_query($count);
$co = mysql_result($result,$x);

if ($co == 0) {
$SQL= "INSERT INTO Diary (username, day_id, message) VALUES('$username', '$day', '$message')";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}
else {

$count="SELECT COUNT(username) FROM Diary WHERE username = '$username' AND day_id = '$day'";
$result = mysql_query($count);
$co1 = mysql_result($result,$x);
echo "$co1";
}

if ($co1 == 1) {
$SQL= "UPDATE Diary SET message = '$message' WHERE username = '$username' AND day_id= '$day'";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}
else {
$SQL= "INSERT INTO Diary (username, day_id, message) VALUES('$username', '$day', '$message')";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}I don't speak MySQL, but it seems like this: when the table 'diary' is empty, you are running the script for the first time:co = 0 and co1 = 0

IF co = 0 (yes, it is) THEN
INSERT INTO diary ... -> this is executed
ELSE
SELECT COUNT ... -> this is not executed
END IF

IF co1 = 1 (no, it isn't) THEN
UPDATE diary ... -> this is not executed
ELSE
INSERT INTO diary ... -> this statement performs second insertRunning the script second (and every other) time:IF co = 0 (no, it isn't) THEN
INSERT INTO diary ...
ELSE
SELECT COUNT ... -> it is executed and co1 = 2
END IF

IF co1 = 1 (no, it isn't) THEN
UPDATE diary ... -> this is not executed
ELSE
INSERT INTO diary ... -> this is executed
END IFIf I'm not wrong, this is what happens. But, you didn't say what you wanted to happen ... Anyway, I guess you'll have to adjust logic a little bit.

Migration from v7 to 2000 Q

We use a custom application written by a software company that are no longer
trading.
The back-end of the application runs on SQL7. We are currently implementing
a small business server 2003 system with SQL 2000. I would therefore like to
move the backend onto SQL 2000 (small biz).
I have tried to migrate to the database using the backup option on sql7 and
then restore on sql2000.
The problem I have come across is although it copies the data, it doesn't
copy the stored procs and views over. For this reason (I assume) the front
end stops working. When you run the application it stops asking for the
location of a view.
Is it possible to migrate the stored procs and views? What are my chances of
success. Sorry for the vagueness of the question, but SQL server admin
really isn't my thing. If you need more info please let me know.
TIA,
H
H,
Backup/Restore detach/attach both copy everything in the database including
views and stored procedures. Do you not see the views and stored procedures
in Enterprise Manager when you move from 7 --> 2000? Who owns these views
and stored procedures i.e., dbo?
HTH
Jerry
"H" <walesforthegr@.ndslam.com> wrote in message
news:O$$5AHQyFHA.3812@.TK2MSFTNGP09.phx.gbl...
> We use a custom application written by a software company that are no
> longer trading.
> The back-end of the application runs on SQL7. We are currently
> implementing a small business server 2003 system with SQL 2000. I would
> therefore like to move the backend onto SQL 2000 (small biz).
> I have tried to migrate to the database using the backup option on sql7
> and then restore on sql2000.
> The problem I have come across is although it copies the data, it doesn't
> copy the stored procs and views over. For this reason (I assume) the front
> end stops working. When you run the application it stops asking for the
> location of a view.
> Is it possible to migrate the stored procs and views? What are my chances
> of success. Sorry for the vagueness of the question, but SQL server admin
> really isn't my thing. If you need more info please let me know.
> TIA,
> H
>
|||Take a look at the Copy Database Wizard which should help pick up objects,
such as Stored Procedures, stored in the Master database.
Hal Berenson, President
PredictableIT, LLC
"H" <walesforthegr@.ndslam.com> wrote in message
news:O$$5AHQyFHA.3812@.TK2MSFTNGP09.phx.gbl...
> We use a custom application written by a software company that are no
> longer trading.
> The back-end of the application runs on SQL7. We are currently
> implementing a small business server 2003 system with SQL 2000. I would
> therefore like to move the backend onto SQL 2000 (small biz).
> I have tried to migrate to the database using the backup option on sql7
> and then restore on sql2000.
> The problem I have come across is although it copies the data, it doesn't
> copy the stored procs and views over. For this reason (I assume) the front
> end stops working. When you run the application it stops asking for the
> location of a view.
> Is it possible to migrate the stored procs and views? What are my chances
> of success. Sorry for the vagueness of the question, but SQL server admin
> really isn't my thing. If you need more info please let me know.
> TIA,
> H
>

Migration from v7 to 2000 Q

We use a custom application written by a software company that are no longer
trading.
The back-end of the application runs on SQL7. We are currently implementing
a small business server 2003 system with SQL 2000. I would therefore like to
move the backend onto SQL 2000 (small biz).
I have tried to migrate to the database using the backup option on sql7 and
then restore on sql2000.
The problem I have come across is although it copies the data, it doesn't
copy the stored procs and views over. For this reason (I assume) the front
end stops working. When you run the application it stops asking for the
location of a view.
Is it possible to migrate the stored procs and views? What are my chances of
success. Sorry for the vagueness of the question, but SQL server admin
really isn't my thing. If you need more info please let me know.
TIA,
HH,
Backup/Restore detach/attach both copy everything in the database including
views and stored procedures. Do you not see the views and stored procedures
in Enterprise Manager when you move from 7 --> 2000? Who owns these views
and stored procedures i.e., dbo?
HTH
Jerry
"H" <walesforthegr@.ndslam.com> wrote in message
news:O$$5AHQyFHA.3812@.TK2MSFTNGP09.phx.gbl...
> We use a custom application written by a software company that are no
> longer trading.
> The back-end of the application runs on SQL7. We are currently
> implementing a small business server 2003 system with SQL 2000. I would
> therefore like to move the backend onto SQL 2000 (small biz).
> I have tried to migrate to the database using the backup option on sql7
> and then restore on sql2000.
> The problem I have come across is although it copies the data, it doesn't
> copy the stored procs and views over. For this reason (I assume) the front
> end stops working. When you run the application it stops asking for the
> location of a view.
> Is it possible to migrate the stored procs and views? What are my chances
> of success. Sorry for the vagueness of the question, but SQL server admin
> really isn't my thing. If you need more info please let me know.
> TIA,
> H
>|||Take a look at the Copy Database Wizard which should help pick up objects,
such as Stored Procedures, stored in the Master database.
--
Hal Berenson, President
PredictableIT, LLC
"H" <walesforthegr@.ndslam.com> wrote in message
news:O$$5AHQyFHA.3812@.TK2MSFTNGP09.phx.gbl...
> We use a custom application written by a software company that are no
> longer trading.
> The back-end of the application runs on SQL7. We are currently
> implementing a small business server 2003 system with SQL 2000. I would
> therefore like to move the backend onto SQL 2000 (small biz).
> I have tried to migrate to the database using the backup option on sql7
> and then restore on sql2000.
> The problem I have come across is although it copies the data, it doesn't
> copy the stored procs and views over. For this reason (I assume) the front
> end stops working. When you run the application it stops asking for the
> location of a view.
> Is it possible to migrate the stored procs and views? What are my chances
> of success. Sorry for the vagueness of the question, but SQL server admin
> really isn't my thing. If you need more info please let me know.
> TIA,
> H
>

Migration from v7 to 2000 Q

We use a custom application written by a software company that are no longer
trading.
The back-end of the application runs on SQL7. We are currently implementing
a small business server 2003 system with SQL 2000. I would therefore like to
move the backend onto SQL 2000 (small biz).
I have tried to migrate to the database using the backup option on sql7 and
then restore on sql2000.
The problem I have come across is although it copies the data, it doesn't
copy the stored procs and views over. For this reason (I assume) the front
end stops working. When you run the application it stops asking for the
location of a view.
Is it possible to migrate the stored procs and views? What are my chances of
success. Sorry for the vagueness of the question, but SQL server admin
really isn't my thing. If you need more info please let me know.
TIA,
HH,
Backup/Restore detach/attach both copy everything in the database including
views and stored procedures. Do you not see the views and stored procedures
in Enterprise Manager when you move from 7 --> 2000? Who owns these views
and stored procedures i.e., dbo?
HTH
Jerry
"H" <walesforthegr@.ndslam.com> wrote in message
news:O$$5AHQyFHA.3812@.TK2MSFTNGP09.phx.gbl...
> We use a custom application written by a software company that are no
> longer trading.
> The back-end of the application runs on SQL7. We are currently
> implementing a small business server 2003 system with SQL 2000. I would
> therefore like to move the backend onto SQL 2000 (small biz).
> I have tried to migrate to the database using the backup option on sql7
> and then restore on sql2000.
> The problem I have come across is although it copies the data, it doesn't
> copy the stored procs and views over. For this reason (I assume) the front
> end stops working. When you run the application it stops asking for the
> location of a view.
> Is it possible to migrate the stored procs and views? What are my chances
> of success. Sorry for the vagueness of the question, but SQL server admin
> really isn't my thing. If you need more info please let me know.
> TIA,
> H
>|||Take a look at the Copy Database Wizard which should help pick up objects,
such as Stored Procedures, stored in the Master database.
Hal Berenson, President
PredictableIT, LLC
"H" <walesforthegr@.ndslam.com> wrote in message
news:O$$5AHQyFHA.3812@.TK2MSFTNGP09.phx.gbl...
> We use a custom application written by a software company that are no
> longer trading.
> The back-end of the application runs on SQL7. We are currently
> implementing a small business server 2003 system with SQL 2000. I would
> therefore like to move the backend onto SQL 2000 (small biz).
> I have tried to migrate to the database using the backup option on sql7
> and then restore on sql2000.
> The problem I have come across is although it copies the data, it doesn't
> copy the stored procs and views over. For this reason (I assume) the front
> end stops working. When you run the application it stops asking for the
> location of a view.
> Is it possible to migrate the stored procs and views? What are my chances
> of success. Sorry for the vagueness of the question, but SQL server admin
> really isn't my thing. If you need more info please let me know.
> TIA,
> H
>