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

Mining Approach?

I have a scenario in mind and was wondering if anyone had any suggestions on an approach.

Lets say I have a dataset where I have captured various attributes about blade servers in a rack. I have internal temperature, fan speed, disk reads, cpu temperature and failure events. Each of these are continuous variables except for the failure event, which has only two states, 'true' or 'false' which indicates whether a failure occurred at that point in time.

The table looks like this:

MachineID Timestamp Temperature FanSpeed Reads CPUTemp Failure

At the time of failure, only the MachineID, Timestamp and Failure values will be populated in the table. The failures at this time are not categorized or discriminated from each other in any way although they will be in the future. We're looking to use a mining algorithm to determine which variable is the best predictor of failure, or combination of variables. What do you think is the right approach? How might this approach change once the failures do become categorized and differentiated?

Thanks,

Frank

This is an interesting problem and I would do some data preperation beforehand to solve this one.

Basically the problem comes to defining your "case". In this problem I would define a case as a "Machine/Time Period". The next problem is what do you want to predict? For example, is predicting failure at the current timestamp useful? Probably not. You probably want to predict failures ahead of time. This leads us to the problem of granularity. Is the timestamp too granular to be interesting? Should you summarize the data to a higher level first?

Let's say you want to predict if a failure will occur within the next five minutes based on minute-by-minute data for the last five minutes. In this situation you would need to aggregate your data at the minute level and create a "FailureInTheNextFiveMinutes" column. The previous five minutes would be represented as a nested table e.g.

Case Table

MachineID/Minute FailureInNextFiveMinutes

NestedTable

MachineID/Minute Minute Termperature FanSpeed Reads CPUTemp

In the nested table the first column is the reference to the case id and the "Minute" column is an integer 1-5 representing how long ago the row represents (e.g. 1 minute ago, 2 minutes ago). You would use the Minute column as the key.

Such a model would give you insights such as "If there are X number of reads 3 minutes ago and the temperature is above Y 1 minute ago you have a 60% chance of failure in the next five minutes".

Obviously you should tune this to fit your needs, but that's the general idea.

HTH

-Jamie

|||

Thanks for the reply.

Your suggestion confirms the direction we decided to take, in fact we probably simplfied it a little bit.

We're feeding a Neural Network with some 7 day aggregates (min,max,avg,std,var) on the different attributes we're capturing 14 days before failures. The hope is that we'll be able to predict issues 7 days before they actually occur, hence the historical snapshot.

I'm happy to report that the algorithm is performing fine, we're in the process of tweaking what we feed the algorithm to come up with the best prediction accuracy.

Frank

|||

I have another point of view for this problem that suppose you can collect accurately training data sets.

Suppose you can get the following row periodically
MachineID Minute Termperature FanSpeed Reads CPUTemp CollectData Failure

where CollectData mean current data/time and Failure has the default value "No"

When the failure is done you have to have the last row before the crash so for that last row the value for Failure
column can be put "Yes". After you have an appropriate training data set (as a volume of data) you can build a data mining model with Failure as predictive column.

Gigi Ciubuc

www.sqlserver.ro

Migration Of MSSQL instance along with security permissions

Hi All,

This is the scenario. Basically i work for a web hosting company now we have taken over control of a competitor and would like to migrate there existing data to our own MSSQL server's in order to integrate them into our automatic system etc etc ...

The current system is running MSSQL7.

The system been copied to is MSSQL2K.

Objectives:

Retain all security accounts listed on the MSSQL7 system and use them on the MSSQL2K system. This includes all the logins that use standard MSSQL login's. (Reason : To ensure that all the customers ASP pages work after the DB's have been copied.)
Backup all the databases on the MSSQL7 and restore them to the MSSQL2K system. Unfortunately system is running on a different network with firewall's etc in between. What type of maintenance plan and how should i implement the requirement to keep all the login account's?
Any help greatly appreciated......
thxone of the option can be like take all backups on either hdd or tape then connect it to ur server copy it from previous one to new one.
to copy all login info etc u can write a script if u need the script i can send u next time (this will take the info to a cursor and one by one it wil update.)
as far as sql 7 database schema and 2k schema is concerned i dont think it will be a problem.
this is an intersting case pl update me|||Hi ranjan apologies for not replying sooner (did not know if anyone had replied). I agree when u state that u dont think either that the difference between porting to 2000 is going to cause an issue. I have restored all the databases now but am in need of a little help / inspiration on how to port over the login names. Perhaps you can help or have a script that performs this task. Regards the script u have for updating i presume its independent to the current logins that are on the SQL2K system at present. How is this done?

Migrating to SQL Server 2005 and Clustering

I have a fairly unique scenario, and I would like to see if anyone can point
out any potential holes of difficulties with this migration plan:
What client has -- 1 SQL Server 2000 enterprise Edition running on SQL 2003
NOT clustered, and using a SCSI disk array.
What client wants -- 2 SQL Server 2005 systems, running on a 2 node cluster
(Active-Passive) using a shared FiberChannel SAN. The active system will be
new, the failover system will be current system listed above. Note that
Clustering HCL has already been verified.
Goals -- provide ability to perform a gradual, testable migration from SQL
Server 2000 to SQL Server 2005 Cluster
Plan:
1) Setup New Server and SAN. Connect Both New server and old server to SAN
2) Install MS Clustering service on both servers, keeping original
un-clustered SQL 2000 in production
3) Install new instance of SQL 2005 on cluster
4) Test and then migrate databases from SQL Server 2000 to new SQL Server
2005 cluster one at a time. This means running the non-clustered SQL 2000 on
the physical hardware as the clustered SQL 2005 failover node. I realize
that failover probably wont work well during the transition, due to a lack of
resources.
5) When transition is complete, remove SQL Server 2000 from failover node.
Any thoughts or comments would be greatly appreciated.
Thanks,
Paul
My only concern is that you are reusing the existing node as a cluster node.
For simplicity's sake, I would create a a clustered installation without
reusing any of the existing hardware. You then have the original system
completely untouched during the transition. You don't need to connect the
existing SQL server to the SAN to do a minimal down time transfer. Given
proper set up time, I can do a server cutover in less than 15 minutes,
regardless of the total database size or count. Since you are moving one DB
at a time, it even gets easier.
If you have to reuse the existing hardware, I would add it to the cluster
after migration is complete. Install the cluster to a single-node, perform
the migration/cutover, and then reload the OS and add the node to the
cluster. You won't have failover redundancy until the project is complete,
but since you don't have that now, you won't miss it.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"PMartin" <PMartin@.discussions.microsoft.com> wrote in message
news:413AB596-D87F-4EFD-93E3-5335FAC3D647@.microsoft.com...
>I have a fairly unique scenario, and I would like to see if anyone can
>point
> out any potential holes of difficulties with this migration plan:
> What client has -- 1 SQL Server 2000 enterprise Edition running on SQL
> 2003
> NOT clustered, and using a SCSI disk array.
> What client wants -- 2 SQL Server 2005 systems, running on a 2 node
> cluster
> (Active-Passive) using a shared FiberChannel SAN. The active system will
> be
> new, the failover system will be current system listed above. Note that
> Clustering HCL has already been verified.
> Goals -- provide ability to perform a gradual, testable migration from SQL
> Server 2000 to SQL Server 2005 Cluster
> Plan:
> 1) Setup New Server and SAN. Connect Both New server and old server to
> SAN
> 2) Install MS Clustering service on both servers, keeping original
> un-clustered SQL 2000 in production
> 3) Install new instance of SQL 2005 on cluster
> 4) Test and then migrate databases from SQL Server 2000 to new SQL Server
> 2005 cluster one at a time. This means running the non-clustered SQL 2000
> on
> the physical hardware as the clustered SQL 2005 failover node. I realize
> that failover probably wont work well during the transition, due to a lack
> of
> resources.
> 5) When transition is complete, remove SQL Server 2000 from failover
> node.
> Any thoughts or comments would be greatly appreciated.
> Thanks,
> Paul
>
|||Geoff,
Unfortunately, we do need to re-use the hardware, so your suggestion of
installing a single node cluster sounds like oustanding advice. The lack of
failover during the transition will not be critical.
You mentioned reloading the OS on the current (soon to be failover) server.
Is this neceessary with Windows 2003, or just wise a precaution?
Thanks again!
Paul
"Geoff N. Hiten" wrote:

> My only concern is that you are reusing the existing node as a cluster node.
> For simplicity's sake, I would create a a clustered installation without
> reusing any of the existing hardware. You then have the original system
> completely untouched during the transition. You don't need to connect the
> existing SQL server to the SAN to do a minimal down time transfer. Given
> proper set up time, I can do a server cutover in less than 15 minutes,
> regardless of the total database size or count. Since you are moving one DB
> at a time, it even gets easier.
> If you have to reuse the existing hardware, I would add it to the cluster
> after migration is complete. Install the cluster to a single-node, perform
> the migration/cutover, and then reload the OS and add the node to the
> cluster. You won't have failover redundancy until the project is complete,
> but since you don't have that now, you won't miss it.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "PMartin" <PMartin@.discussions.microsoft.com> wrote in message
> news:413AB596-D87F-4EFD-93E3-5335FAC3D647@.microsoft.com...
>
>
|||I have prefer clean OS installs over upgrades. I have never personally had
any problems with 2000-2003 upgrades, but I have had issues with earlier OS
upgrade paths. I have even go so far as to upgrade each node in a cluster
then go back and wipe and reload each node, just so I don't have any
leftover bits. Since you will be migrating a W2000/SQL 2000 standalone host
to a W2003/SQL2005 clustered host, I would definitely wipe the system and
start over. If nothing else, you can guarantee it is an exact OS match for
its peer node.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"PMartin" <PMartin@.discussions.microsoft.com> wrote in message
news:F8076304-BBFE-4447-B0D8-D7A39F0544D4@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Unfortunately, we do need to re-use the hardware, so your suggestion of
> installing a single node cluster sounds like oustanding advice. The lack
> of
> failover during the transition will not be critical.
> You mentioned reloading the OS on the current (soon to be failover)
> server.
> Is this neceessary with Windows 2003, or just wise a precaution?
> Thanks again!
> Paul
> "Geoff N. Hiten" wrote:
|||I don't see anything wrong with this. It's the way that I've upgraded more
than one system already.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"PMartin" <PMartin@.discussions.microsoft.com> wrote in message
news:413AB596-D87F-4EFD-93E3-5335FAC3D647@.microsoft.com...
>I have a fairly unique scenario, and I would like to see if anyone can
>point
> out any potential holes of difficulties with this migration plan:
> What client has -- 1 SQL Server 2000 enterprise Edition running on SQL
> 2003
> NOT clustered, and using a SCSI disk array.
> What client wants -- 2 SQL Server 2005 systems, running on a 2 node
> cluster
> (Active-Passive) using a shared FiberChannel SAN. The active system will
> be
> new, the failover system will be current system listed above. Note that
> Clustering HCL has already been verified.
> Goals -- provide ability to perform a gradual, testable migration from SQL
> Server 2000 to SQL Server 2005 Cluster
> Plan:
> 1) Setup New Server and SAN. Connect Both New server and old server to
> SAN
> 2) Install MS Clustering service on both servers, keeping original
> un-clustered SQL 2000 in production
> 3) Install new instance of SQL 2005 on cluster
> 4) Test and then migrate databases from SQL Server 2000 to new SQL Server
> 2005 cluster one at a time. This means running the non-clustered SQL 2000
> on
> the physical hardware as the clustered SQL 2005 failover node. I realize
> that failover probably wont work well during the transition, due to a lack
> of
> resources.
> 5) When transition is complete, remove SQL Server 2000 from failover
> node.
> Any thoughts or comments would be greatly appreciated.
> Thanks,
> Paul
>