Mirroring Failover leaves 2 databases as Principal

We have seven databases mirrrored on our production server. Two of
these failed over, one correctly. The other database is marked as
"Principal/Disconnected" on BOTH the primary and secondary servers. I
can browse the tables in each database. Obviously this is not meant to
happen - one of the databases should be in Restoring mode.Transactions
are only going into the one database - on the Primary server. Has
anyone come across this before? Any ideas on how to proceed?
If I attempt to pause mirroring on the primary server it fails with
the message "The database mirror is busy, re-issue the command later".
I can see this getting really bad if I am unable to (at worst) remove
mirroring from either database due to them thinking that they are the
principal and cannot connect to the other mirrored database.
(both servers on SP2.)On Sep 19, 7:03 pm, Calculated <sarahjco...@.gmail.com> wrote:
> We have seven databases mirrrored on our production server. Two of
> these failed over, one correctly. The other database is marked as
> "Principal/Disconnected" on BOTH the primary and secondary servers. I
> can browse the tables in each database. Obviously this is not meant to
> happen - one of the databases should be in Restoring mode.Transactions
> are only going into the one database - on the Primary server. Has
> anyone come across this before? Any ideas on how to proceed?
> If I attempt to pause mirroring on the primary server it fails with
> the message "The database mirror is busy, re-issue the command later".
> I can see this getting really bad if I am unable to (at worst) remove
> mirroring from either database due to them thinking that they are the
> principal and cannot connect to the other mirrored database.
> (both servers on SP2.)
My plan was to come in and backup both databases and then restart the
SQL Service on the secondary, hoping that the state would change.
However when I got in to check this (maybe 6 hours later), the
database on the Secondary had elevated itself to be the Principal
(connected) and the Primay server's database was Synchronised/
Restoring. Good. I have since manually failed the databases back over
to primary. All seems ok so far but I will need to check this
throughout the day. (Hope no-one else runs into this!)

Mirroring databases connected through a View

I have two databases db_A_primary and db_B_primary, both databases are on one Primary server.

db_B_primary has a View into db_A_primary.

Scenario: db_A_primary goes down and failsover to db_A_mirror on the Mirror server.

In this scenario when the View in db_B_primary is accessed will it automatically be redirected to look at the db_A_mirror database on the Mirror server?

Barry.

When the database fails over, it won't change the contents of the database. If you want the view to always point to a specific server.database.owner.table, you will want to specify it that way in the application. In other cases, you might want the view to point to a local table and wouldn't want to point to a remote server after failover. The bottom line is that it is up to the application to set the behavior desired.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||

Your mirrored database can not have a different name from the principal database. Perhaps you meant Server Name?

You can't do this with a view, but in stored procedures, you can use a try/catch to execute a dynamic query on the live server.

Begin Try

Exec sp_executesql N'Select * From db_A_primary.dbo.ATable'

End Try

Begin Catch

Exec sp_executesql N'Select * From MirrorServer.db_A_primary.dbo.ATable'

End Catch

Mirroring and replication

Hi MSSQL friend,

We have a SQL2005 server which contains some replicated databases. We use merge replication and the publications are all local.

In order to make the server failsafe we need some kind of mirroring. Is this possible with replication?

Any ideas how to accomplish a failsafe scenario with replication?

Sincerely Edward

You can start by searching SQL Server Books Online, I searched for "replication mirroring" and found this:

Replication and Database Mirroring - http://msdn2.microsoft.com/en-us/library/ms151799.aspx

|||Thanks Greg!sql

Mirroring and logins

Hi,
I am maintaining a mirroring set up that I did not create. I have two
boxes with the same logins and 15-20 mirrored databases. My issue is
that I know some of the sids for SQL logins are different between the
boxes so that when we fail over in a crisis, some of the apps are not
going to be able to connect because of the ophaned login process. Is
there any way that I can run an automated process/script to find out
in advance what sids don't match. We have over 100 SQL logins that
exist on each server but may be mis-matched. I don't want to mirror
over and run the EXEC sp_change_users_login for each user on each
database.
Thanks in advance...
Kristina
> over and run the EXEC sp_change_users_login for each user on each
> database.
If you have SP2 you ca use ALTER USER username WITH LOGIN ='login'
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
> Hi,
> I am maintaining a mirroring set up that I did not create. I have two
> boxes with the same logins and 15-20 mirrored databases. My issue is
> that I know some of the sids for SQL logins are different between the
> boxes so that when we fail over in a crisis, some of the apps are not
> going to be able to connect because of the ophaned login process. Is
> there any way that I can run an automated process/script to find out
> in advance what sids don't match. We have over 100 SQL logins that
> exist on each server but may be mis-matched. I don't want to mirror
> over and run the EXEC sp_change_users_login for each user on each
> database.
> Thanks in advance...
|||On Apr 16, 10:30Xam, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Kristina> over and run the EXEC sp_change_users_login for each user Xon each
> If you have SP2 you ca use ALTER USER username WITH LOGIN ='login'
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
>
>
>
> - Show quoted text -
I don't think that will do the trick. On a mirrored instance the
databases are in a restoring mode so we can't do that. The databases
are not accessible.
|||Perhaps you can create a snapshot of your mirrored database? That should get you to
sys.database_principals (in the snapshot database) which you can check against
sys.server_principals.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
> Hi,
> I am maintaining a mirroring set up that I did not create. I have two
> boxes with the same logins and 15-20 mirrored databases. My issue is
> that I know some of the sids for SQL logins are different between the
> boxes so that when we fail over in a crisis, some of the apps are not
> going to be able to connect because of the ophaned login process. Is
> there any way that I can run an automated process/script to find out
> in advance what sids don't match. We have over 100 SQL logins that
> exist on each server but may be mis-matched. I don't want to mirror
> over and run the EXEC sp_change_users_login for each user on each
> database.
> Thanks in advance...
|||On Apr 16, 10:26Xpm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Perhaps you can create a snapshot of your mirrored database? That should get you to
> sys.database_principals (in the snapshot database) which you can check against
> sys.server_principals.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
>
>
>
> - Show quoted text -
GREAT IDEA!!! Thanks! Now I know how to solve my problelm. I don't
know why I didn't think of this myself.

Mirroring and logins

Hi,
I am maintaining a mirroring set up that I did not create. I have two
boxes with the same logins and 15-20 mirrored databases. My issue is
that I know some of the sids for SQL logins are different between the
boxes so that when we fail over in a crisis, some of the apps are not
going to be able to connect because of the ophaned login process. Is
there any way that I can run an automated process/script to find out
in advance what sids don't match. We have over 100 SQL logins that
exist on each server but may be mis-matched. I don't want to mirror
over and run the EXEC sp_change_users_login for each user on each
database.
Thanks in advance...Kristina
> over and run the EXEC sp_change_users_login for each user on each
> database.
If you have SP2 you ca use ALTER USER username WITH LOGIN ='login'
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
> Hi,
> I am maintaining a mirroring set up that I did not create. I have two
> boxes with the same logins and 15-20 mirrored databases. My issue is
> that I know some of the sids for SQL logins are different between the
> boxes so that when we fail over in a crisis, some of the apps are not
> going to be able to connect because of the ophaned login process. Is
> there any way that I can run an automated process/script to find out
> in advance what sids don't match. We have over 100 SQL logins that
> exist on each server but may be mis-matched. I don't want to mirror
> over and run the EXEC sp_change_users_login for each user on each
> database.
> Thanks in advance...|||On Apr 16, 10:30=A0am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Kristina> over and run the EXEC sp_change_users_login for each user =A0on =each
> > database.
> If you have SP2 you ca use ALTER USER username WITH LOGIN =3D'login'
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
>
> > Hi,
> > I am maintaining a mirroring set up that I did not create. I have two
> > boxes with the same logins and 15-20 mirrored databases. My issue is
> > that I know some of the sids for SQL logins are different between the
> > boxes so that when we fail over in a crisis, some of the apps are not
> > going to be able to connect because of the ophaned login process. Is
> > there any way that I can run an automated process/script to find out
> > in advance what sids don't match. We have over 100 SQL logins that
> > exist on each server but may be mis-matched. I don't want to mirror
> > over and run the EXEC sp_change_users_login for each user =A0on each
> > database.
> > Thanks in advance...- Hide quoted text -
> - Show quoted text -
I don't think that will do the trick. On a mirrored instance the
databases are in a restoring mode so we can't do that. The databases
are not accessible.|||Perhaps you can create a snapshot of your mirrored database? That should get you to
sys.database_principals (in the snapshot database) which you can check against
sys.server_principals.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
> Hi,
> I am maintaining a mirroring set up that I did not create. I have two
> boxes with the same logins and 15-20 mirrored databases. My issue is
> that I know some of the sids for SQL logins are different between the
> boxes so that when we fail over in a crisis, some of the apps are not
> going to be able to connect because of the ophaned login process. Is
> there any way that I can run an automated process/script to find out
> in advance what sids don't match. We have over 100 SQL logins that
> exist on each server but may be mis-matched. I don't want to mirror
> over and run the EXEC sp_change_users_login for each user on each
> database.
> Thanks in advance...|||On Apr 16, 10:26=A0pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Perhaps you can create a snapshot of your mirrored database? That should g=et you to
> sys.database_principals (in the snapshot database) which you can check aga=inst
> sys.server_principals.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:d00cdbcb-7985-4c4b-b3fe-b8ef64d8eeb4@.m3g2000hsc.googlegroups.com...
>
> > Hi,
> > I am maintaining a mirroring set up that I did not create. I have two
> > boxes with the same logins and 15-20 mirrored databases. My issue is
> > that I know some of the sids for SQL logins are different between the
> > boxes so that when we fail over in a crisis, some of the apps are not
> > going to be able to connect because of the ophaned login process. Is
> > there any way that I can run an automated process/script to find out
> > in advance what sids don't match. We have over 100 SQL logins that
> > exist on each server but may be mis-matched. I don't want to mirror
> > over and run the EXEC sp_change_users_login for each user =A0on each
> > database.
> > Thanks in advance...- Hide quoted text -
> - Show quoted text -
GREAT IDEA!!! Thanks! Now I know how to solve my problelm. I don't
know why I didn't think of this myself.

Mirroring

We are moving our asp application from SQL2k to SQL2k5. I implemented
database mirroring for my critical databases, which is setup & running. I
modified the connection string in the Global.asa to point to my new servers.
When both SQL servers are up & running I connect to the primary database.
However, when the server hosting the primary database is stopped, I get an
error regarding the connection string.
Is this an application error that needs to be resolved? What do I tell the
developers to do?
Is this a problem with the SQL connection string? Does anyone have working
examples?
Also, I am using a witness server.
Does anyone have any test procedures/scripts/scenarios to insure mirroring
is working as it should. I thought I could stop SQL on a server run a test,
restart SQL & then check the databases.
I'm ok at server & database admin but poor at database programming. And
brand new to SQL2k5 mirroring.
Thanks"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> We are moving our asp application from SQL2k to SQL2k5. I implemented
> database mirroring for my critical databases, which is setup & running. I
> modified the connection string in the Global.asa to point to my new
> servers.
> When both SQL servers are up & running I connect to the primary database.
> However, when the server hosting the primary database is stopped, I get an
> error regarding the connection string.
> Is this an application error that needs to be resolved? What do I tell the
> developers to do?
> Is this a problem with the SQL connection string? Does anyone have working
> examples?
> Also, I am using a witness server.
> Does anyone have any test procedures/scripts/scenarios to insure mirroring
> is working as it should. I thought I could stop SQL on a server run a
> test,
> restart SQL & then check the databases.
> I'm ok at server & database admin but poor at database programming. And
> brand new to SQL2k5 mirroring.
>
Does your connection string specify both the principal and the mirror? If
not, failover will work, but startup when the principal is down will not.
See:
System.Data.SqlConnection.ConnectionString Property
Failover Partner
N/A
The name of the failover partner server where database mirroring is
configured.
The Failover Partner keyword is not supported by .NET Framework version 1.0
or 1.1.
http://msdn2.microsoft.com/en-us/li...tionstring.aspx
David|||"David Browne" wrote:

>
> "BigSam" <BigSam@.discussions.microsoft.com> wrote in message
> news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> Does your connection string specify both the principal and the mirror? If
> not, failover will work, but startup when the principal is down will not.
> See:
> System.Data.SqlConnection.ConnectionString Property
> Failover Partner
> N/A
> The name of the failover partner server where database mirroring is
> configured.
> The Failover Partner keyword is not supported by .NET Framework version 1.
0
> or 1.1.
> http://msdn2.microsoft.com/en-us/li...tionstring.aspx
>
> David
> This is the connection string set in the Global.asa:
Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
Also, we're not using asp.net - just plain old ASP.|||"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:E89D67CA-4BA6-4A87-B053-F3F10BC1D3AC@.microsoft.com...
>
> "David Browne" wrote:
>
> Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
> Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
> Also, we're not using asp.net - just plain old ASP.
>
The OleDb provider you're using isn't mirroring-aware. The SQLNCLI the new
SqlServer Native Client provider, if it works correctly with your
application. See:
Using ADO with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130978.aspx
If you stick with SQLOLEDB, then you can use DNS or BigIP to manually switch
the traffic between servers, or just change your config and boune the site.
David|||Thanks for your reply.
Yesterday & was able to determine that without mirroring I could connect to
each server with the SQL login. However, with mirroring enabled I could only
connect to SQL1 with the SQL login. I began to suspect the SID & found this
article: http://support.microsoft.com/default.aspx/kb/918992/
"Magas" wrote:
[vbcol=seagreen]
> If you use SQL Server login to connect to the mirror database - make sure
> logins on both mirror servers have the same SID. The easiest way to do it
-
> copy login from one server to another using SSIS package.
>
> "BigSam" wrote:
>

Mirroring

We are moving our asp application from SQL2k to SQL2k5. I implemented
database mirroring for my critical databases, which is setup & running. I
modified the connection string in the Global.asa to point to my new servers.
When both SQL servers are up & running I connect to the primary database.
However, when the server hosting the primary database is stopped, I get an
error regarding the connection string.
Is this an application error that needs to be resolved? What do I tell the
developers to do?
Is this a problem with the SQL connection string? Does anyone have working
examples?
Also, I am using a witness server.
Does anyone have any test procedures/scripts/scenarios to insure mirroring
is working as it should. I thought I could stop SQL on a server run a test,
restart SQL & then check the databases.
I'm ok at server & database admin but poor at database programming. And
brand new to SQL2k5 mirroring.
Thanks
"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> We are moving our asp application from SQL2k to SQL2k5. I implemented
> database mirroring for my critical databases, which is setup & running. I
> modified the connection string in the Global.asa to point to my new
> servers.
> When both SQL servers are up & running I connect to the primary database.
> However, when the server hosting the primary database is stopped, I get an
> error regarding the connection string.
> Is this an application error that needs to be resolved? What do I tell the
> developers to do?
> Is this a problem with the SQL connection string? Does anyone have working
> examples?
> Also, I am using a witness server.
> Does anyone have any test procedures/scripts/scenarios to insure mirroring
> is working as it should. I thought I could stop SQL on a server run a
> test,
> restart SQL & then check the databases.
> I'm ok at server & database admin but poor at database programming. And
> brand new to SQL2k5 mirroring.
>
Does your connection string specify both the principal and the mirror? If
not, failover will work, but startup when the principal is down will not.
See:
System.Data.SqlConnection.ConnectionString Property
Failover Partner
N/A
The name of the failover partner server where database mirroring is
configured.
The Failover Partner keyword is not supported by .NET Framework version 1.0
or 1.1.
[url]http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstri ng.aspx[/url]
David
|||"David Browne" wrote:

>
> "BigSam" <BigSam@.discussions.microsoft.com> wrote in message
> news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> Does your connection string specify both the principal and the mirror? If
> not, failover will work, but startup when the principal is down will not.
> See:
> System.Data.SqlConnection.ConnectionString Property
> Failover Partner
> N/A
> The name of the failover partner server where database mirroring is
> configured.
> The Failover Partner keyword is not supported by .NET Framework version 1.0
> or 1.1.
> [url]http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstri ng.aspx[/url]
>
> David
> This is the connection string set in the Global.asa:
Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
Also, we're not using asp.net - just plain old ASP.
|||"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:E89D67CA-4BA6-4A87-B053-F3F10BC1D3AC@.microsoft.com...
>
> "David Browne" wrote:
> Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
> Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
> Also, we're not using asp.net - just plain old ASP.
>
The OleDb provider you're using isn't mirroring-aware. The SQLNCLI the new
SqlServer Native Client provider, if it works correctly with your
application. See:
Using ADO with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130978.aspx
If you stick with SQLOLEDB, then you can use DNS or BigIP to manually switch
the traffic between servers, or just change your config and boune the site.
David
|||Thanks for your reply.
Yesterday & was able to determine that without mirroring I could connect to
each server with the SQL login. However, with mirroring enabled I could only
connect to SQL1 with the SQL login. I began to suspect the SID & found this
article: http://support.microsoft.com/default.aspx/kb/918992/
"Magas" wrote:
[vbcol=seagreen]
> If you use SQL Server login to connect to the mirror database - make sure
> logins on both mirror servers have the same SID. The easiest way to do it -
> copy login from one server to another using SSIS package.
>
> "BigSam" wrote:
sql

mirrored sql servers

Hello:
I have 2 MS SQL Servers 2000 Developer edition.
Both have same settings and databases. I'd like to have them as mirrored
servers - all transaction on one server must be performed on the other
automatically. More than that I'd like to get possible if something happened
to one server, all transactions will be performed on another one.
Please, give me a hint how to do that.

Thanks,
GBGB (v7v1k3@.hotmail.com) writes:
> I have 2 MS SQL Servers 2000 Developer edition.
> Both have same settings and databases. I'd like to have them as mirrored
> servers - all transaction on one server must be performed on the other
> automatically. More than that I'd like to get possible if something
> happened to one server, all transactions will be performed on another
> one.

Symmetric or one-way? If one server is a main server, and the other is a
standby server, you can use either log shipping or transactional
replication. Log shipping is easier to manage, as transactional
replication leaves its mark on the source database. On the other hand,
the standby server will not be accessible each time a log is applied,
and the lag time is longer.

If it is symmetric, that actions are to be replicated in both directions,
then you may want to look into merge replication. But this sounds anything
about trivial to me. What about if the same row was updated in both
databases? Who wins?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The only purpose I have is to have a "backup" server to be ready
to substitute main server at any time with recent data in it
and "seamless" switching in production mode.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns970419D43A88Yazorman@.127.0.0.1...
> GB (v7v1k3@.hotmail.com) writes:
> > I have 2 MS SQL Servers 2000 Developer edition.
> > Both have same settings and databases. I'd like to have them as mirrored
> > servers - all transaction on one server must be performed on the other
> > automatically. More than that I'd like to get possible if something
> > happened to one server, all transactions will be performed on another
> > one.
> Symmetric or one-way? If one server is a main server, and the other is a
> standby server, you can use either log shipping or transactional
> replication. Log shipping is easier to manage, as transactional
> replication leaves its mark on the source database. On the other hand,
> the standby server will not be accessible each time a log is applied,
> and the lag time is longer.
> If it is symmetric, that actions are to be replicated in both directions,
> then you may want to look into merge replication. But this sounds anything
> about trivial to me. What about if the same row was updated in both
> databases? Who wins?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||GB (v7v1k3@.hotmail.com) writes:
> The only purpose I have is to have a "backup" server to be ready
> to substitute main server at any time with recent data in it
> and "seamless" switching in production mode.

OK. The I would suggest log shipping.

However, you cannot really switch into real production mode with
Developer Edition, as you don't have a license for it. But it is
of course, perfectly OK to set up log shipping to learn how to
use it for a production scenario.

Oh, I should add that once you come to production, you may only
have Standard or Workgroup Edition. Log Shipping is included in
Enterprise Edition only, so for these edition replication is about
the only option.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In message <Xns97047DCD3077Yazorman@.127.0.0.1>, Erland Sommarskog
<esquel@.sommarskog.se> writes
>GB (v7v1k3@.hotmail.com) writes:
>> The only purpose I have is to have a "backup" server to be ready
>> to substitute main server at any time with recent data in it
>> and "seamless" switching in production mode.
>OK. The I would suggest log shipping.
>However, you cannot really switch into real production mode with
>Developer Edition, as you don't have a license for it. But it is
>of course, perfectly OK to set up log shipping to learn how to
>use it for a production scenario.
>Oh, I should add that once you come to production, you may only
>have Standard or Workgroup Edition. Log Shipping is included in
>Enterprise Edition only, so for these edition replication is about
>the only option.

If GB wants clustering and failover I think he will need the enterprise
edition.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.|||Bernard Peek (bap@.shrdlu.com) writes:
> If GB wants clustering and failover I think he will need the enterprise
> edition.

That is correct. (OK, since Developer Edition has all that Enterprise
Edition has, you can put that on a cluster. But if you put Developer
Edition on a cluster, then your hardware budget for your development
environment is quite excessive.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Bernard Peek" <bap@.shrdlu.com> wrote in message
news:ahtXDqTk30aDFwG3@.shrdlu.com...
> In message <Xns97047DCD3077Yazorman@.127.0.0.1>, Erland Sommarskog
> <esquel@.sommarskog.se> writes
> >GB (v7v1k3@.hotmail.com) writes:
> >> The only purpose I have is to have a "backup" server to be ready
> >> to substitute main server at any time with recent data in it
> >> and "seamless" switching in production mode.
> >OK. The I would suggest log shipping.
> >However, you cannot really switch into real production mode with
> >Developer Edition, as you don't have a license for it. But it is
> >of course, perfectly OK to set up log shipping to learn how to
> >use it for a production scenario.
> >Oh, I should add that once you come to production, you may only
> >have Standard or Workgroup Edition. Log Shipping is included in
> >Enterprise Edition only, so for these edition replication is about
> >the only option.
> If GB wants clustering and failover I think he will need the enterprise
> edition.

SQL Server 2005 will be released with DB mirroring, though in a "beta form".

He may want to look at this.

> --
> Bernard Peek
> London, UK. DBA, Manager, Trainer & Author.

Mirrored databases and Witness server

We are upgrading to 2005 and have decided to go to a mirrored 2005
environment. We have picked out the production server (principal) 16
gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
having difficulty finding out how "beefy" a box we need for the Witness
server. Does anyone have any experience with this and can you assist me
in specs. I am working with our Network Administrator.
Thanks for all the help!
KristinaAm 11 Jan 2007 06:32:20 -0800 schrieb KristinaDBA@.gmail.com:

> We are upgrading to 2005 and have decided to go to a mirrored 2005
> environment. We have picked out the production server (principal) 16
> gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> having difficulty finding out how "beefy" a box we need for the Witness
> server. Does anyone have any experience with this and can you assist me
> in specs. I am working with our Network Administrator.
> Thanks for all the help!
> Kristina
Hi, the whitness Server don't need to be so big ;-)
You should install the right editon of SQL Server to be Witness.
The relevance of your sizing are the other roles/jobs this server will do
beside beeing witness.
thats my opinion. hope I could help a little
greetings
Rouven|||Hi,
Kristina - your witness server can be a single processor box with
1/2 GB memory. You can even use SQL Server express. The resources
needed for the witness server is minimum.
This from our Microsoft pre-sales rep.
hth
KristinaDBA@.gmail.com wrote:
> We are upgrading to 2005 and have decided to go to a mirrored 2005
> environment. We have picked out the production server (principal) 16
> gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> having difficulty finding out how "beefy" a box we need for the Witness
> server. Does anyone have any experience with this and can you assist me
> in specs. I am working with our Network Administrator.
> Thanks for all the help!
> Kristina|||Hey - thanks everyone for the help! I love this group!
JoeyD wrote:[vbcol=seagreen]
> Hi,
> Kristina - your witness server can be a single processor box with
> 1/2 GB memory. You can even use SQL Server express. The resources
> needed for the witness server is minimum.
> This from our Microsoft pre-sales rep.
> hth
>
> KristinaDBA@.gmail.com wrote:|||While being a witness is not a very hard job, it is important because when
the witness is down, automatic failover won't happen so while you can get by
without a lot of processor or memory resources, you don't want to use your
kid's old game machine for this. Get a good quality machine with a good NIC
card and maybe even a UPS.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<KristinaDBA@.gmail.com> wrote in message
news:1168528668.044990.221330@.i56g2000hsf.googlegroups.com...
> Hey - thanks everyone for the help! I love this group!
> JoeyD wrote:
>

Mirrored databases and Witness server

We are upgrading to 2005 and have decided to go to a mirrored 2005
environment. We have picked out the production server (principal) 16
gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
having difficulty finding out how "beefy" a box we need for the Witness
server. Does anyone have any experience with this and can you assist me
in specs. I am working with our Network Administrator.
Thanks for all the help!
Kristina
Hi,
Kristina - your witness server can be a single processor box with
1/2 GB memory. You can even use SQL Server express. The resources
needed for the witness server is minimum.
This from our Microsoft pre-sales rep.
hth
KristinaDBA@.gmail.com wrote:
> We are upgrading to 2005 and have decided to go to a mirrored 2005
> environment. We have picked out the production server (principal) 16
> gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> having difficulty finding out how "beefy" a box we need for the Witness
> server. Does anyone have any experience with this and can you assist me
> in specs. I am working with our Network Administrator.
> Thanks for all the help!
> Kristina
|||Hey - thanks everyone for the help! I love this group!
JoeyD wrote:[vbcol=seagreen]
> Hi,
> Kristina - your witness server can be a single processor box with
> 1/2 GB memory. You can even use SQL Server express. The resources
> needed for the witness server is minimum.
> This from our Microsoft pre-sales rep.
> hth
>
> KristinaDBA@.gmail.com wrote:
|||While being a witness is not a very hard job, it is important because when
the witness is down, automatic failover won't happen so while you can get by
without a lot of processor or memory resources, you don't want to use your
kid's old game machine for this. Get a good quality machine with a good NIC
card and maybe even a UPS.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<KristinaDBA@.gmail.com> wrote in message
news:1168528668.044990.221330@.i56g2000hsf.googlegr oups.com...
> Hey - thanks everyone for the help! I love this group!
> JoeyD wrote:
>

Mirrored databases and Witness server

We are upgrading to 2005 and have decided to go to a mirrored 2005
environment. We have picked out the production server (principal) 16
gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
having difficulty finding out how "beefy" a box we need for the Witness
server. Does anyone have any experience with this and can you assist me
in specs. I am working with our Network Administrator.
Thanks for all the help!
KristinaAm 11 Jan 2007 06:32:20 -0800 schrieb KristinaDBA@.gmail.com:
> We are upgrading to 2005 and have decided to go to a mirrored 2005
> environment. We have picked out the production server (principal) 16
> gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> having difficulty finding out how "beefy" a box we need for the Witness
> server. Does anyone have any experience with this and can you assist me
> in specs. I am working with our Network Administrator.
> Thanks for all the help!
> Kristina
Hi, the whitness Server don't need to be so big ;-)
You should install the right editon of SQL Server to be Witness.
The relevance of your sizing are the other roles/jobs this server will do
beside beeing witness.
thats my opinion. hope I could help a little
greetings
Rouven|||Hi,
Kristina - your witness server can be a single processor box with
1/2 GB memory. You can even use SQL Server express. The resources
needed for the witness server is minimum.
This from our Microsoft pre-sales rep.
hth
KristinaDBA@.gmail.com wrote:
> We are upgrading to 2005 and have decided to go to a mirrored 2005
> environment. We have picked out the production server (principal) 16
> gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> having difficulty finding out how "beefy" a box we need for the Witness
> server. Does anyone have any experience with this and can you assist me
> in specs. I am working with our Network Administrator.
> Thanks for all the help!
> Kristina|||Hey - thanks everyone for the help! I love this group!
JoeyD wrote:
> Hi,
> Kristina - your witness server can be a single processor box with
> 1/2 GB memory. You can even use SQL Server express. The resources
> needed for the witness server is minimum.
> This from our Microsoft pre-sales rep.
> hth
>
> KristinaDBA@.gmail.com wrote:
> > We are upgrading to 2005 and have decided to go to a mirrored 2005
> > environment. We have picked out the production server (principal) 16
> > gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> > having difficulty finding out how "beefy" a box we need for the Witness
> > server. Does anyone have any experience with this and can you assist me
> > in specs. I am working with our Network Administrator.
> >
> > Thanks for all the help!
> >
> > Kristina|||While being a witness is not a very hard job, it is important because when
the witness is down, automatic failover won't happen so while you can get by
without a lot of processor or memory resources, you don't want to use your
kid's old game machine for this. Get a good quality machine with a good NIC
card and maybe even a UPS.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<KristinaDBA@.gmail.com> wrote in message
news:1168528668.044990.221330@.i56g2000hsf.googlegroups.com...
> Hey - thanks everyone for the help! I love this group!
> JoeyD wrote:
>> Hi,
>> Kristina - your witness server can be a single processor box with
>> 1/2 GB memory. You can even use SQL Server express. The resources
>> needed for the witness server is minimum.
>> This from our Microsoft pre-sales rep.
>> hth
>>
>> KristinaDBA@.gmail.com wrote:
>> > We are upgrading to 2005 and have decided to go to a mirrored 2005
>> > environment. We have picked out the production server (principal) 16
>> > gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
>> > having difficulty finding out how "beefy" a box we need for the Witness
>> > server. Does anyone have any experience with this and can you assist me
>> > in specs. I am working with our Network Administrator.
>> >
>> > Thanks for all the help!
>> >
>> > Kristina
>

Mirror Setup Problems

We are having problems getting the two databases to connect with each other for mirroring. We have the following setup:

- Doing Principal and Mirror (no witness)

- Using Management Studio

- Both servers are SQL Server 2005 SP1 Standard Edition

- Same domain account, same user id

- We can telnet to both and connect to both from Stuio.

We were originally getting error 1418, but now we're getting:

"Neither the partner nor the witness server instance for database "tmsng" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"

This is what the log file looks like:

2006-11-06 13:01:59.62 spid24s Error: 1443, Severity: 16, State: 2.

2006-11-06 13:01:59.62 spid24s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 13:03:32.98 spid17s Error: 1474, Severity: 16, State: 1.

2006-11-06 13:03:32.98 spid17s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

2006-11-06 13:53:18.14 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:20.15 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:20.15 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 13:53:29.79 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:31.79 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:31.79 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 14:21:43.48 spid24s Error: 9642, Severity: 16, State: 3.

2006-11-06 14:21:43.48 spid24s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')

2006-11-06 14:47:38.79 spid22s Error: 1443, Severity: 16, State: 2.

2006-11-06 14:47:38.79 spid22s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 14:49:23.06 spid22s Error: 1474, Severity: 16, State: 1.

2006-11-06 14:49:23.06 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

Here’s an update:

I tried doing mirroring via the command line.

Creating endpoints went OK, backup and restore went OK.

Setting partner went OK from the mirror to principal, but blew up for principal to mirror:

ALTER DATABASE test1

SET PARTNER = ‘tcp://srvr1.xxx.net.com:5022’;

Msg 1418, Level 16, State 1, Server SRVR1, Line 1

The server network address "tcp://srvr1.xxx.net.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

Name is right, ports are listening, endpoints are started. Why would it go from A to B but not B to A?

Thanks.

update again - problem more specific, starting a new thread.

|||I had the same problem, I have noticed that principal tries to connect to the mirror using 'NT AUTHORITY\ANONIMOUS ACCESS' (look at the errorlog file in C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\LOG), so I just added that user with sysadmin role and everything works fine|||

I solved the problem by changing the way that SQL server services (SQL server and Agent) start, instead of local system account i used domain user account that had rights on both servers !

Best regards
Nikola

Mirror Setup Problems

We are having problems getting the two databases to connect with each other for mirroring. We have the following setup:

- Doing Principal and Mirror (no witness)

- Using Management Studio

- Both servers are SQL Server 2005 SP1 Standard Edition

- Same domain account, same user id

- We can telnet to both and connect to both from Stuio.

We were originally getting error 1418, but now we're getting:

"Neither the partner nor the witness server instance for database "tmsng" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"

This is what the log file looks like:

2006-11-06 13:01:59.62 spid24s Error: 1443, Severity: 16, State: 2.

2006-11-06 13:01:59.62 spid24s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 13:03:32.98 spid17s Error: 1474, Severity: 16, State: 1.

2006-11-06 13:03:32.98 spid17s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

2006-11-06 13:53:18.14 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:20.15 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:20.15 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 13:53:29.79 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:31.79 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:31.79 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 14:21:43.48 spid24s Error: 9642, Severity: 16, State: 3.

2006-11-06 14:21:43.48 spid24s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')

2006-11-06 14:47:38.79 spid22s Error: 1443, Severity: 16, State: 2.

2006-11-06 14:47:38.79 spid22s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 14:49:23.06 spid22s Error: 1474, Severity: 16, State: 1.

2006-11-06 14:49:23.06 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

Here’s an update:

I tried doing mirroring via the command line.

Creating endpoints went OK, backup and restore went OK.

Setting partner went OK from the mirror to principal, but blew up for principal to mirror:

ALTER DATABASE test1

SET PARTNER = ‘tcp://srvr1.xxx.net.com:5022’;

Msg 1418, Level 16, State 1, Server SRVR1, Line 1

The server network address "tcp://srvr1.xxx.net.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

Name is right, ports are listening, endpoints are started. Why would it go from A to B but not B to A?

Thanks.

update again - problem more specific, starting a new thread.

|||I had the same problem, I have noticed that principal tries to connect to the mirror using 'NT AUTHORITY\ANONIMOUS ACCESS' (look at the errorlog file in C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\LOG), so I just added that user with sysadmin role and everything works fine|||

I solved the problem by changing the way that SQL server services (SQL server and Agent) start, instead of local system account i used domain user account that had rights on both servers !

Best regards
Nikola

Mirror Setup Problems

We are having problems getting the two databases to connect with each other for mirroring. We have the following setup:

- Doing Principal and Mirror (no witness)

- Using Management Studio

- Both servers are SQL Server 2005 SP1 Standard Edition

- Same domain account, same user id

- We can telnet to both and connect to both from Stuio.

We were originally getting error 1418, but now we're getting:

"Neither the partner nor the witness server instance for database "tmsng" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"

This is what the log file looks like:

2006-11-06 13:01:59.62 spid24s Error: 1443, Severity: 16, State: 2.

2006-11-06 13:01:59.62 spid24s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 13:03:32.98 spid17s Error: 1474, Severity: 16, State: 1.

2006-11-06 13:03:32.98 spid17s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

2006-11-06 13:53:18.14 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:20.15 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:20.15 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 13:53:29.79 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:31.79 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:31.79 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 14:21:43.48 spid24s Error: 9642, Severity: 16, State: 3.

2006-11-06 14:21:43.48 spid24s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')

2006-11-06 14:47:38.79 spid22s Error: 1443, Severity: 16, State: 2.

2006-11-06 14:47:38.79 spid22s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 14:49:23.06 spid22s Error: 1474, Severity: 16, State: 1.

2006-11-06 14:49:23.06 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

Here’s an update:

I tried doing mirroring via the command line.

Creating endpoints went OK, backup and restore went OK.

Setting partner went OK from the mirror to principal, but blew up for principal to mirror:

ALTER DATABASE test1

SET PARTNER = ‘tcp://srvr1.xxx.net.com:5022’;

Msg 1418, Level 16, State 1, Server SRVR1, Line 1

The server network address "tcp://srvr1.xxx.net.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

Name is right, ports are listening, endpoints are started. Why would it go from A to B but not B to A?

Thanks.

update again - problem more specific, starting a new thread.

|||I had the same problem, I have noticed that principal tries to connect to the mirror using 'NT AUTHORITY\ANONIMOUS ACCESS' (look at the errorlog file in C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\LOG), so I just added that user with sysadmin role and everything works fine|||

I solved the problem by changing the way that SQL server services (SQL server and Agent) start, instead of local system account i used domain user account that had rights on both servers !

Best regards
Nikola

sql

Mirror of master, msdb, tempdb, or model databases.

Hi Guys,

Since,You cannot mirror the master, msdb, tempdb, or model databases.What will happen if i create new login,change existing security profile and new jobs,change of existing job on princicpal db. how these will be mirrored to other server and in case failover, how it will treat.

Thaks

Mirroring works at the database level only. dts/ssis packages, jobs etc are not mirrored. Database users are mirrored but not their corresponding SQL Server login.

You need to keep a copy of dts/ssis packages, jobs etc on the mirror server and whatever changes you make to them on the principal server, you will also have to make on the mirror server if you want to keep the servers exactly in sync.

hope this helps

mirror as a publisher

Hello,
Is it possible to setup replication on mirrored database? Using the naming
‘principal’ and ‘mirror’ for databases involved in mirroring and ‘publisher’
and ‘subscriber’ for replication, what I exactly need is to configure mirror
database as a publisher.
My motivation is as follows:
Currently, I have 2 applications (X and Y), each using its own database (Xdb
and Ydb). Both applications run in the same geographical location (Loc1),
both databases run in the same instance of SQL Server. Some tables are
replicated from Xdb to Ydb.
I need to move Y application to a different geographical location (Loc2) and
in the same time, I want to define some disaster recovery scenario in this
way:
Both applications will be installed in both locations. Y application is
sleeping in Loc1 and X is sleeping in Loc2. Xdb is mirrored from Loc1 to Loc2
and Ydb is mirrored from Loc2 to Loc1. As some data have to be replicated
from Xdb to Ydb I want to save the line between Loc1 and Loc2 and want to
have the replication locally on Loc2 (from mirror of Xdb into Ydb).
Thanks
eXavier
Hello eXavier,
Regarding your question, based on my research, it's not supported to
configure "mirrored" database as the publisher since it's a standby server.
You could only configure publisher in principle database. You may want to
refer to the following article for more details:
Replication and Database Mirroring
http://msdn2.microsoft.com/en-us/library/ms151799.aspx
You could configure snapshot of mirrored database but it's read only and
could not be configured as publisher as well.
Using Database Snapshots with Database Mirroring
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0bf1be90-7ce4-484c-aaa7-f8a7
82f57c5f.htm
Please rest assure that your feedback on this feature is routed to the
proper channel. Also, I encourage you submit via the link below and our
product team would like to hear your voice:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
If you have any further questions or comments, please feel free to let's
know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Minimum Setup Requirements for SQL 2005 Replication.

What are the minimum setup requirements to support the following.
2 Remote Laptops running SQL Server 2005 Express databases with the ability
to synchronize with each other. Also they should be able to synch with a
server hosting a SQL Database whci is accessible from 2 other Desktop PCs.
What are the SQL CAL Licensing issues that would apply to this scenario.
Regards
Unfortunately SQL Server Express can only be a subscriber to all types of
replication. All subscriptions must be created through RMO or replication
stored procedures.
Regarding your desktop pc's - If they are running workgroup edition they can
have up to 25 merge subscribers, but 5 transactional subscribers. In regards
to licensing anything which accesses the desktop must have a license, which
can be a CAL or a server per processor license (which will not make a whole
lot of sense).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stuart Lowe" <StuartLowe@.discussions.microsoft.com> wrote in message
news:6C1471E6-61F1-4BF2-AAA8-16E67A29A967@.microsoft.com...
> What are the minimum setup requirements to support the following.
> 2 Remote Laptops running SQL Server 2005 Express databases with the
> ability
> to synchronize with each other. Also they should be able to synch with a
> server hosting a SQL Database whci is accessible from 2 other Desktop PCs.
> What are the SQL CAL Licensing issues that would apply to this scenario.
> Regards
sql

Minimum permission to attach / detach databases

Hi group,
I've got a problem determining permissions for users. I got one group
of users which only should have the abilitiy to attach / detach any
database to the SQL Server.
I just thought the SQL server wide user role dbcreator seems to be
sufficient for them but if I try to attach a database on which this
group does not have any permission within (its a database from an
external vendor), attaching the database fails.
Any hints on this?
Thanks,
HeikoHeiko
Can you elaborate a little bit?
>but if I try to attach a database on which this
> group does not have any permission within (its a database from an
> external vendor), attaching the database fails.
>
"Heiko Schuler" <hschuler@.gmail.com> wrote in message
news:1192693343.587584.291710@.v29g2000prd.googlegroups.com...
> Hi group,
> I've got a problem determining permissions for users. I got one group
> of users which only should have the abilitiy to attach / detach any
> database to the SQL Server.
> I just thought the SQL server wide user role dbcreator seems to be
> sufficient for them but if I try to attach a database on which this
> group does not have any permission within (its a database from an
> external vendor), attaching the database fails.
>
> Any hints on this?
>
> Thanks,
>
> Heiko
>|||> Can you elaborate a little bit?
Sure - I got one user which has the database role "dbcreator". When I
now try to attach a database, I got an error. the strange thing is - I
got 2 different depending using the GUI or T-SQL.
When I use the GUI, after selecting the mdf file, I got the following
error:
"User 'guest' does not have permission to run DBCC checkprimeryfile.
OK, this error message itself is clear because only members of the
sysadmin or server admin role can run DBCC commands. But why does the
GUI wants to run DBCC checkprimeryfile?
When I try to attach the database via T-SQL with sp_attach_db in Query
Analyzer, I got the following error:
"Unable to open the physical fiel <path to the mdf>. Operating system
error 5 (error not found).
Depending on the message error message above I first thougt it was a
permisson error on the file system. But if I put the user in the
sysadmin server role - everything works fine. So NTFS permission on
the file system is OK.
Heiko

Minimum Permission on Objects - Eliminate Dependent Objects in Ownership Chain--sp_depends

I have several databases. In those databases, collectively I have
several hundreds objects. All these objects are owned by the dbo. I
would like to find:

Item (a)

a sql script or some automated way of finding the minimum set of
permissions so users accessing the database via applications can
successfully do their job.

CASE 1:
For example, say in one of the databases, DB1, I have a Stored
Procedure called SP1, which depends on 10 objects (views, tables, and
other Stored Procedures). I would like to give only Execute permission
on SP1, and leave permissioning on all dependent objects alone. Since
all dependent objects are owned by dbo, Sql Server will bypass
permission check, once it successfully checks permission on SP1.

CASE 2:
I have another stored procedure SP2, which deletes some rows from
table T4. Again, I would like to only give execute permission on SP2,
and not touch permissioning on T4.

CASE 3:
A form within an application references a view V1, which depends on
table T6. Only select and updates are allowed on V1.

So, in CASE 1, 2 and 3 above, I would like a sql script, which lists
only SP1, SP2, and V1 and the required permissions. This way I know I
only need to give execute permission on SP1 and SP2, and select and
update permission on V1.
Ideally, a script which then assigns these permissions to a role is
even better.

Item (b)

As objects are added to the databases, a similar code as in item (a),
but this time only gives me the objects, which require minimum
persssion and are not in (a) above. So, in this item (b), I am only
looking at objects not shown in (a) above.

I am aware of sp_depends, but not sure how to generate a sql script to
do the above jobs. I would appreciate if someone could give me
pointers, or some samples, which can be used to construct the above
script. Additionally, in conjunction with sp_MSforeachdb, the script
can then generate the results for all the databases.gudia (gudia97@.yahoo.com) writes:
> Item (a)
> a sql script or some automated way of finding the minimum set of
> permissions so users accessing the database via applications can
> successfully do their job.
> CASE 1:
> For example, say in one of the databases, DB1, I have a Stored
> Procedure called SP1, which depends on 10 objects (views, tables, and
> other Stored Procedures). I would like to give only Execute permission
> on SP1, and leave permissioning on all dependent objects alone. Since
> all dependent objects are owned by dbo, Sql Server will bypass
> permission check, once it successfully checks permission on SP1.
> CASE 2:
> I have another stored procedure SP2, which deletes some rows from
> table T4. Again, I would like to only give execute permission on SP2,
> and not touch permissioning on T4.
> CASE 3:
> A form within an application references a view V1, which depends on
> table T6. Only select and updates are allowed on V1.
> So, in CASE 1, 2 and 3 above, I would like a sql script, which lists
> only SP1, SP2, and V1 and the required permissions. This way I know I
> only need to give execute permission on SP1 and SP2, and select and
> update permission on V1.
> Ideally, a script which then assigns these permissions to a role is
> even better.

I may be missing something, but I cannot see how you could build such
a script. Such a script would have to know your application and see
what it actually acesses.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 2 Dec 2004 22:42:15 +0000 (UTC), Erland Sommarskog wrote:

> gudia (gudia97@.yahoo.com) writes:
>> Item (a)
>>
>> a sql script or some automated way of finding the minimum set of
>> permissions so users accessing the database via applications can
>> successfully do their job.
>>
>> CASE 1:
>> For example, say in one of the databases, DB1, I have a Stored
>> Procedure called SP1, which depends on 10 objects (views, tables, and
>> other Stored Procedures). I would like to give only Execute permission
>> on SP1, and leave permissioning on all dependent objects alone. Since
>> all dependent objects are owned by dbo, Sql Server will bypass
>> permission check, once it successfully checks permission on SP1.
>>
>> CASE 2:
>> I have another stored procedure SP2, which deletes some rows from
>> table T4. Again, I would like to only give execute permission on SP2,
>> and not touch permissioning on T4.
>>
>> CASE 3:
>> A form within an application references a view V1, which depends on
>> table T6. Only select and updates are allowed on V1.
>>
>> So, in CASE 1, 2 and 3 above, I would like a sql script, which lists
>> only SP1, SP2, and V1 and the required permissions. This way I know I
>> only need to give execute permission on SP1 and SP2, and select and
>> update permission on V1.
>> Ideally, a script which then assigns these permissions to a role is
>> even better.
> I may be missing something, but I cannot see how you could build such
> a script. Such a script would have to know your application and see
> what it actually acesses.

I was thinking about this off and on, and I think something like it should
theoretically be possible. If this table defines what access we ultimately
want:

CREATE TABLE Desired_Activity (
OBJECT_TYPE varchar(5), -- 'Table', 'view', 'proc', etc.
OBJECT_NAME varchar(64), -- object's name
DESIRED_ACCESS varchar(64), 'SELECT','UPDATE','DELETE','EXECUTE'
)

then the magic procedure examines the dependencies of each object listed,
figures out the minimal way (or maybe only close to the minimal way; the
problem seems quite hard) to arrange the permissions into roles, and prints
the role definitions.

That way, the script doesn't have to know the application and what it does,
just what you tell it your application does. The rest of what it needs
should be in the dependency information.|||Hi Erland and Ross,

Thanks for your responses. I have myself been thinking about this
general problem. Any solution to this same problem can be helpful to
different people in different ways. One quick query, which I thought
about, has saved me sometime. It is as follows. I have excluded Tables
from this sql.

select name, sysobjects.id from sysobjects inner join (select distinct
id, cnt=count(id) from sysdepends group by id having count(id)>1)
DepObj on DepObj.id=sysobjects.id
where type in ('P','V','FN') and category=0
order by name

This sql gives me all the objects with dependencies.

Ross, going per your idea or in that direction, would be one way of
building accessibility of the apps to the DB, and securing database(s)
via explicit permissioning, which I am hoping could be managed easily
by a DBA or that group.

Erland, your point is valid that the script would not know by itself
what permissioning to give to the objects. I did not express myself
very well. I am still trying to figure out the best way to structure
and manage permissioning on the database(s).

Another question I have is related to managing permissioning of the
entire set of distinct databases residing on multiple servers,
possibly from one centralized location.

By the way, Erland and Ross, how do you deal with permissioning, in
your applications, in terms of both the manageability (the structure
holding the permissions) and keeping the permissions minimum?

Your efforts in steering me in the right direction is helpful.

Ross Presser <rpresser@.imtek.com> wrote in message news:<13zc1e6jo9uwv$.dlg@.rpresser.invalid>...
> On Thu, 2 Dec 2004 22:42:15 +0000 (UTC), Erland Sommarskog wrote:
> > gudia (gudia97@.yahoo.com) writes:
> >> Item (a)
> >>
> >> a sql script or some automated way of finding the minimum set of
> >> permissions so users accessing the database via applications can
> >> successfully do their job.
> >>
> >> CASE 1:
> >> For example, say in one of the databases, DB1, I have a Stored
> >> Procedure called SP1, which depends on 10 objects (views, tables, and
> >> other Stored Procedures). I would like to give only Execute permission
> >> on SP1, and leave permissioning on all dependent objects alone. Since
> >> all dependent objects are owned by dbo, Sql Server will bypass
> >> permission check, once it successfully checks permission on SP1.
> >>
> >> CASE 2:
> >> I have another stored procedure SP2, which deletes some rows from
> >> table T4. Again, I would like to only give execute permission on SP2,
> >> and not touch permissioning on T4.
> >>
> >> CASE 3:
> >> A form within an application references a view V1, which depends on
> >> table T6. Only select and updates are allowed on V1.
> >>
> >> So, in CASE 1, 2 and 3 above, I would like a sql script, which lists
> >> only SP1, SP2, and V1 and the required permissions. This way I know I
> >> only need to give execute permission on SP1 and SP2, and select and
> >> update permission on V1.
> >> Ideally, a script which then assigns these permissions to a role is
> >> even better.
> > I may be missing something, but I cannot see how you could build such
> > a script. Such a script would have to know your application and see
> > what it actually acesses.
> I was thinking about this off and on, and I think something like it should
> theoretically be possible. If this table defines what access we ultimately
> want:
> CREATE TABLE Desired_Activity (
> OBJECT_TYPE varchar(5), -- 'Table', 'view', 'proc', etc.
> OBJECT_NAME varchar(64), -- object's name
> DESIRED_ACCESS varchar(64), 'SELECT','UPDATE','DELETE','EXECUTE'
> )
> then the magic procedure examines the dependencies of each object listed,
> figures out the minimal way (or maybe only close to the minimal way; the
> problem seems quite hard) to arrange the permissions into roles, and prints
> the role definitions.
> That way, the script doesn't have to know the application and what it does,
> just what you tell it your application does. The rest of what it needs
> should be in the dependency information.|||gudia (gudia97@.yahoo.com) writes:
> Thanks for your responses. I have myself been thinking about this
> general problem. Any solution to this same problem can be helpful to
> different people in different ways. One quick query, which I thought
> about, has saved me sometime. It is as follows. I have excluded Tables
> from this sql.
> select name, sysobjects.id from sysobjects inner join (select distinct
> id, cnt=count(id) from sysdepends group by id having count(id)>1)
> DepObj on DepObj.id=sysobjects.id
> where type in ('P','V','FN') and category=0
> order by name
> This sql gives me all the objects with dependencies.

Beware that sysdepends is far from a reliable source. For instance, if you
alter a procedure, all rows for references to this procedure are wiped out
from sysdepends.

You should not build any critical infrastructure on sysdepends. Sysdepends
can sometimes be useful, but you need to be aware of its limitations.

> By the way, Erland and Ross, how do you deal with permissioning, in
> your applications, in terms of both the manageability (the structure
> holding the permissions) and keeping the permissions minimum?

Our approach is actually very simple-minded, a carry-over from 4.x days.
Basically all users supposed to belong to a group, eh role as it's called
these days, dvp, and this group is granted EXECUTE rights on all procedures
and functions and SELECT rights on all tables.

However, we have the tools to do this more fine-grained if we wish. We
keep all our code under source-control, and each subsystem - as we call
it - has its SQL file in a certain structure. In the top level of this
structure is a file grant.template. This file is parsed by our load tool,
and from this file, the tool determines which GRANT statements to run
for the object.

So we could list individual objects in this file if we wanted to. However,
if there is some object that requires some special permission, we prefer
to put the GRANT statement in the file for that object, and the
grant.template files has the general rules.

Overall, I would say that in a large and complex system you cannot afford
to have too complex rules, because with 3700 stored procedures, it is
inevitable that you will get errors unless you apply some standard rules.

It may be more worth the effort to have a fine-grained control for tables
and views. In our case, we give SELECT rights since we use dynamic SQL.
But we are quite conservative in the usage of dynamic SQL, and it would be
quite conceivable that we would have a rule that says "No SELECT access,
unless there is a need for dynamic SQL on that table".

I should that our application has its own permission system, which is
separate from what SQL Server offers. This permission system controls
which GUI forms which users have access to, and our application comes
with forms for our customers to maintain that permission system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sun, 5 Dec 2004 17:44:09 +0000 (UTC), Erland Sommarskog wrote:

> Beware that sysdepends is far from a reliable source. For instance, if you
> alter a procedure, all rows for references to this procedure are wiped out
> from sysdepends.
> You should not build any critical infrastructure on sysdepends. Sysdepends
> can sometimes be useful, but you need to be aware of its limitations.

Is sysdepends reliable on a cleanly recreated database? In other words, if
I script all objects, then recreate them in a new database in order (using
"create schema authorization dbo" so I can create the tables and views
together without caring about dependency, then doing the procedures and
functions), will sysdepends then report correctly?

(I'm begging the question here ... I tried doing this and it seemed to
work.)|||Ross Presser (rpresser@.imtek.com) writes:
> Is sysdepends reliable on a cleanly recreated database? In other words, if
> I script all objects, then recreate them in a new database in order (using
> "create schema authorization dbo" so I can create the tables and views
> together without caring about dependency, then doing the procedures and
> functions), will sysdepends then report correctly?
> (I'm begging the question here ... I tried doing this and it seemed to
> work.)

If you first create the tables, then the dependencies for the tables
will be in order. Presuming that you don't have constraints that calls
UDFs that refers to tables. For views, functions and stored procedures,
you need to create them in dependency order. That is, if proc A calls B,
and you create A first, this dependency will not be recorded. And since
ALTER PROC wipes out the dependencies, you can work around this by loading
all procedures twice. (ALTER PROC has been fixed in SQL 2005, though.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland and Ross for all the suggestions you have made.

Erland, I agree that for a large system, security has to be implemented
such that it is fairly easy to manage, and yet leave no holes.

Erland Sommarskog wrote:
> Ross Presser (rpresser@.imtek.com) writes:
> > Is sysdepends reliable on a cleanly recreated database? In other
words, if
> > I script all objects, then recreate them in a new database in order
(using
> > "create schema authorization dbo" so I can create the tables and
views
> > together without caring about dependency, then doing the procedures
and
> > functions), will sysdepends then report correctly?
> > (I'm begging the question here ... I tried doing this and it seemed
to
> > work.)
> If you first create the tables, then the dependencies for the tables
> will be in order. Presuming that you don't have constraints that
calls
> UDFs that refers to tables. For views, functions and stored
procedures,
> you need to create them in dependency order. That is, if proc A calls
B,
> and you create A first, this dependency will not be recorded. And
since
> ALTER PROC wipes out the dependencies, you can work around this by
loading
> all procedures twice. (ALTER PROC has been fixed in SQL 2005,
though.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.aspsql

Migration: SQL Server 7.0 - SQL Server 2000

I have to migrate my SQL Server 7 databases to SQL Server 2000.
What I was planning to do was to restore my full 7.0 backup to my new SQL Server 2000 system. This looks to good to be
true so......... Is there a problem with this plan? What are the "gotchas"? What should I look-out for? Is there
a better way of doing this migration.
I've only been a DBA for about a year so I can definitely use all the help I can get. Any ideas, help, suggestions are
greatly appreciated.
tx.
cathy
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:p35jl0ho08595bk909et8eikvamehb889a@.4ax.com...
> I have to migrate my SQL Server 7 databases to SQL Server 2000.
> What I was planning to do was to restore my full 7.0 backup to my new SQL
Server 2000 system. This looks to good to be
> true so......... Is there a problem with this plan? What are the
"gotchas"? What should I look-out for? Is there
> a better way of doing this migration.
That's an excellent way to perform your upgrade.

> I've only been a DBA for about a year so I can definitely use all the help
I can get. Any ideas, help, suggestions are
> greatly appreciated.
Planning is a key part of any DBA's job, so you are on target. If you have
not seen the following links, have a read ;)
How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
http://www.microsoft.com/technet/pro...y/sqlugrd.mspx
Upgrading an Existing Installation of SQL Server
http://msdn.microsoft.com/library/de...rview_2xtf.asp
Steve
|||... and some other links that might be useful:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:%23zStb4XpEHA.324@.TK2MSFTNGP11.phx.gbl...
> "Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
> news:p35jl0ho08595bk909et8eikvamehb889a@.4ax.com...
> Server 2000 system. This looks to good to be
> "gotchas"? What should I look-out for? Is there
> That's an excellent way to perform your upgrade.
> I can get. Any ideas, help, suggestions are
> Planning is a key part of any DBA's job, so you are on target. If you have
> not seen the following links, have a read ;)
> How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
> http://www.microsoft.com/technet/pro...y/sqlugrd.mspx
> Upgrading an Existing Installation of SQL Server
> http://msdn.microsoft.com/library/de...rview_2xtf.asp
> Steve
>
|||Thanks Steve & Tibor. The links are definitely helpful. I always get a
case of the "warm fuzzies" when I have someone with lots of experience
validating my decisions & plans.
cathy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uVdec5XpEHA.516@.TK2MSFTNGP09.phx.gbl...
> ... and some other links that might be useful:
>
> Moving SQL Server Databases
> http://www.support.microsoft.com/?id=224071
> Moving Databases between Servers
> http://www.support.microsoft.com/?id=314546
> Using WITH MOVE in a Restore to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
> How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers[vbcol=seagreen]
> http://www.support.microsoft.com/?id=240872
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Thompson" <stevethompson@.nomail.please> wrote in message
> news:%23zStb4XpEHA.324@.TK2MSFTNGP11.phx.gbl...
message[vbcol=seagreen]
SQL[vbcol=seagreen]
help[vbcol=seagreen]
have[vbcol=seagreen]
http://www.microsoft.com/technet/pro...y/sqlugrd.mspx[vbcol=seagreen]
http://msdn.microsoft.com/library/de...rview_2xtf.asp
>