Mirroring With A Twist
Jul 18, 2007
I'm really new to SQL and looking for advice.
I'm going through the basics I don't mind doing my own research but I have an issue that needs attention and is beyond my current level.
I'm looking for a way to have any data entered into one database automatically copied to an archive database running on another machine. Items deleted in database one would be retained in database two. Database one would be clean and efficient and only reflect currently necessary information. Database two would grow indefinitely as an archive of what had/does exist in database one.
I'm sure this is quite mundane but I'm not sure what key words I'm looking for. Is there a title for this process?
Is this possible from within the SQL management studio or should I start those videos under the developer section?
I'm in the tall grass. Any pointers appreciated.
Both servers are Server2003 with SQL Server 2005 Standard Edition /Sp2
/drew
View 4 Replies
ADVERTISEMENT
Jul 23, 2005
Hi,What is the best way to model this: Assume I have two objects: Agencyand Publisher, and both have a 1-to-n relationship to Employee. This isa true 1-to-n relationship, as each Employee can only work for oneAgency or one Publisher. Let's assume further that I cannot introduce asupertype (e.g. Employer) which holds the 1-to-n relationship.My preferrd solution is to have a foreign key in Emplyee that caneither link to a primary key of Agency or Publisher (all my primarykeys are 64-bit IDs that are unqiue across the database). However, nowI won't be able to map a bi-directional association, without indicatingin Employee whether this is an "Agency" or "Publisher" relationship(ala <ANY>).My other option is to use two tables, AgencyEmployee andPublisherEmployee, which can then be linked as traditional 1-to-nbidirectional associations.What do you guys consider best practice in this situation?Cheers,Jen
View 19 Replies
View Related
May 10, 2007
Hi,I have a requirement to design a query that identifies items soldbetween two dates. There is a 'SoldDate' datetime field used toregister what date the item was sold.The query needs to identify all sales between the last day of theprevious month and going back one year.What I would like to do is to design a query / stored procedure thatwill dynamically create the criteria to allow the client to simply runthe query or stored proc.I know how to establish the last day of the previous month part, I'mjust not sure of how best to design the remainder of the query.Thank in advance
View 9 Replies
View Related
Jul 20, 2005
OK,Here is my challenge.If I have a query that produces the followingItemSold_OnA01-10-2004 8:03A01-11-2004 10:05A01-12-20041:37A01-14-20047:16B01-10-20049:37B01-12-2004 11:42B01-13-20049:37But I need it to produce this insteadItemSold_On InstanceA01-10-2004 8:031A01-11-2004 10:052A01-12-20041:373A01-14-20047:164B01-10-20049:371B01-12-2004 11:422B01-13-20049:373So basically I need it to chronologically number the rows, but I needthe count to start over when the item changes.
View 3 Replies
View Related
Nov 28, 2003
ive got a tough one for everyone this time!
i am trying to get a page up that enables me to enter a number into a box on a form, which then on clicking of a search button, queries the database, and returns 2 other fields that relate to the number found in the database, in an ASP datagrid.
the easiest way to do this would be to use OLEDB or ODBC, but the host i am using doesnt support either of them, and so i am trying to use their code, something using OBJDB, but to no avail. frankly, their code is rubbish, and im not sure how to go about doing the above.
i would be very grateful if someone could help me out with this problem,
thanks,
craig
View 1 Replies
View Related
Apr 9, 2008
:eek:
I'm having a brain not functioning day - well who am I kidding - more like a year :rolleyes:
I need some help with some sequence numbering and cannot even get my head around the logic I want to use, let alone the actual code.
I have a dataset with 3 fields:
Area
ID
RefNo
This table contains a list of employee ID's by Area and each employee has a RefNo (counter) in each area.
The data comes from 2 different sources and is combined in this table. Some employees had no RefNo already assigned to them so I have entered their RefNo as 10000 in order to ensure they are sorted at the bottom of the list.
The ID's that have RefNo's have to keep the one they have. Therefore, I need to create RefNo's for the ones that currently have RefNo 10000.
These numbers I create have to follow on from the highest RefNo for the Area.
For example:
Area ID RefNo
A Z 1
A Y 2
A X 3
A W 10000
A V 10000
B N 1
B O 10000
B P 10000
So, for Area A, ID's W and V would have to be assigned RefNo 4 and 5, and for Area B, ID's O and P would have to be assigned RefNo 2 and 3.
Hope this makes sense to all.
BTW, am using SQL 2000 at the moment.
Thanks in advance for any help!
View 11 Replies
View Related
Mar 25, 2014
I have Two Time fields in a table. Time(0). An "opening time" and a "closing time". They can hold any legit time.
I want to calculate in a SELECT Statement how many minutes within this range are within 9am to 5pm (which I'll convert to hours).
For example, here's an easy example:
OPEN: 9:00:00
CLOSE: 17:00:00
8 Hours/480 minutes
I could get this easy enough with a DATEDIFF function.
But what about:
OPEN: 08:00:00
CLOSE: 18:00:00
10 Hours total but only 8 of those 10 are within 9am-5pm.
Or what about:
OPEN: 10:00:00
CLOSE: 20:00:00
10 Hours total but only 7 are within 9am-5pm range.
I can calculate the total hours/minutes between the two times but not within that special range.
View 4 Replies
View Related
Jun 11, 2007
Hi,
I would like to import an Excel spreadsheet into SQL Server 2005. I can do this quite easily using the Import/Export wizard, and have each row in the spreadsheet transfer to a new row in the database table.
However, I want to import the first few columns of the spreadsheet row into one table (called Products), but put the remaining columns into a related, three-column table, called Product_Details. In the Product_Details table, one column would hold the spreadsheet column value, the other column would be a FK integer value linked to the PK in the Products table, and the third column the primary key as normal.
So, somehow, I would need to get hold of the primary key value when the first spreadsheet columns are inserted into the Products table and then insert the remaining columns into the Product_Details table with two values per row - one value being the spreadsheet cell value, the second being the primary key of the new product in the Products table.
TIA,
Graham.
View 1 Replies
View Related
May 3, 2008
Server A = primary SQL DBs (mirroring origination)
Server B = failover SQL DBs (mirroring destination)
For database mirroring a witness is required.
Can the witness live in another instance of SQL on server B?
View 7 Replies
View Related
Oct 12, 2015
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
View 5 Replies
View Related
Sep 15, 2015
I need to set up asyncronous data replication across two clustered instances of SQL 2012 across 2 Datacenters. Both the datacenters have a common domain however the vlans are different. There are only 3 small databases on the primary instance.
any issue in setting up mirroring in this case as vlans are different.
Operating system is Windows 2012.
View 2 Replies
View Related
Mar 7, 2000
Hi All
By mirroring I mean device mirror, since I do not think that there is a database mirror
We only have disk mirror. I also know that SQLServer 7.0 does not provide Mirror, unmirror or remirror. and for this we have to refer to Windows NT disk mirroring.
I need mirroring in both versions SQL Server 6.5 and 7.0
I want to have mirror on another server. I need this for tonight.
Sincerely.
View 1 Replies
View Related
Apr 1, 2008
I hereby let u know the steps that i undertook for performing mirroring
1. Created a database in Principal server named Mirror1 and one table named tblMirror1.( no values inside the table)
2. Repeated the same in Mirror in Witness
3. Selected the Principal server's database -->right click-->tasks-->mirror
4. from the left hand panel selected Options -->recovery model-->full
5. selected Mirroring from left hand panel-->configure secuirty-->entered mirror and witness name--Success
6. When I click on start mirroring I have an error stating
" Alter failed for database mirror1"
"Database mirroring cannot be enabled because Mirror1 database may have bulk logged changes that have not been backed up etc...."
7. followed this one
BACKUP database mirror1 TO DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'
got the answer as
Processed 160 pages for database 'mirror1', file 'Mirror1' on file 2.
Processed 1 pages for database 'mirror1', file 'Mirror1_log' on file 2.
BACKUP DATABASE successfully processed 161 pages in 0.318 seconds (4.147 MB/sec).
According to sources
Run a restore of this backup on your mirror.
This can be done through the GUI or using a T-SQL command.
The database restore must use the NO RECOVERY option, so the database stays in a loading state.
Also the database name on the mirror must be the exact same name as the principal
RESTORE TestMirror FROM DISK='C:Backup TestMirror_FULL.BAK' WITH NORECOVERY
Hence did the below
RESTORE database mirror1 FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK' WITH NORECOVERY
ERROR
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
HENCE MODIFIED AS
use master
go
RESTORE database mirror1 FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK' WITH NORECOVERY
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
========
Kindly let me know please where am i going wrong
Regards
cmrhema
View 1 Replies
View Related
Mar 13, 2007
hello
if i run dbcc dbreindex on a database thats the principal in a mirrored pair, will the mirrored database indexes also be rebuilt?
View 1 Replies
View Related
Mar 27, 2007
i am for the first time creating the database mirroring.I am moving the all the usr databases and everything from test server to a new server called test1.And from that new server i am restoring on the another new server called test2 for database mirroring.But in the test server , the transaction logs are backup for every 15 minutes.So in this case is there a need to move all the logs to test1 and then to test2 for mirroring?? or there is no need to move transaction logs to the new servers at all??
my secnario
------------
server1 server2 server3
-------- ------- --------
test test1 (moving everything from test) test2(mirror for test1 )
all the transaction logs are backup in test for every 15 minutes. so in this case , do i need to move the transcation logs from test to test1 and test2
or there is not need to move at all??
thanks,
View 1 Replies
View Related
Nov 7, 2007
Can anyone please tell me what mirroring is??
Thanks in ADvance
RKNAIR
View 2 Replies
View Related
Jan 30, 2008
I have sql server 2005 on my vista laptop.
When i set up database mirroring, do i have to set up Principle,
mirror and witness on seperate servers as well as seperate instances.
Or can i install 3 seperate instances on the same laptop im using and still use mirroring? I have tried to start mirroring with all three instances on my laptop but i receive an error.
Regards
Rob
View 1 Replies
View Related
Mar 26, 2008
Hi All,
I am trying to set up database mirroring and in the initial phase of database restoring im getting some problem.
the scenario is .
1- I create a TestMirror on server1(which will be principal server) and backup full and transaction logs.
2- create TestMirror in server 2( Which is going to be used as mirror) .
3- restore the full backup of "TestMirror" from Principle Sever with NORECOVERY option.
this step results in a message box showing that the restore was successfull...
but the database explorer( management studio) shows the TestMirror in (restoring ...) state. and no more operations can be performed with this Database. ( because the database is in restoring mode).. please help what this issue is?
Thanks In Advance for any suggestions
-- Krishna
View 9 Replies
View Related
May 6, 2007
hello
i used following query to create endpoint
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_A_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
);
GO
i got the following error msg:
Msg 1498, Level 16, State 2, Line 11
Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online.
any help plzz
thanks in advance
View 3 Replies
View Related
Aug 22, 2007
Hello,
I have Microsoft SQL SERVER 2005 installed in my system.
To try out mirroring, I rightclicked on the database went to the tasks, but i found that in Status its written
"This database has not been configured for mirroring"
What shall I do now?
View 5 Replies
View Related
Oct 22, 2007
Hi,
I am new to DB mirroring and witness. For the purpose of learning, any one can suggest me like is there any posibility to do the DB mirroring and witness on 2 or 3 VHD Files. Basically, these VHD's are downloaded from microsoft website.
VHD's have W2K3 and SQL2K5 EE .
Please suggest me in this.
Regards,
Mohanraj Jayaraman
View 3 Replies
View Related
Nov 10, 2006
Hello Guys,
Can any one help I want SQL to email me when a database fails over I can find alerts for loads of thing to do with Mirroring but not to notify me when a database failover.
Thanks matt
View 1 Replies
View Related
Nov 23, 2006
How sql server will mirror big transaction ?
Example
inserting 10000000 rows in 1 transaction (columns big ,text data ...)
will it transfer data in multiple packets (size, location of temp file) to mirror ?
will it transfer data in 1 packet (size , location of temp file ) to mirror ?
Can you control transfer (size , location of temp file) ?
Thanks
Alex
View 1 Replies
View Related
Sep 28, 1998
Do you know if the following excerpt from technet is still true? I want to mirror a device over the net to another server. Has anyone tried this with sql server 6.5 or is it still not a feature?
----------------------
PSS ID Number: Q115043
Article last modified on 04-29-1997
4.2 | 4.2
OS/2 WINDOWS
================================================== ====================
----------------------------------------------------------------------
The information in this article applies to:
- Microsoft SQL Server version 4.2 for OS/2
- Microsoft SQL Server version 4.2
----------------------------------------------------------------------
The use of database devices accessed over the network is not a supported
configuration for either the OS/2 or Windows NT versions of Microsoft SQL
Server. All database devices, including mirrors, should be configured to
use local drives. Database dump devices are an exception to this
restriction.
----------------
Thanks.
Gail Wade
Raymond James and Associates
Clearwater, FL
gwade@is.rjf.com
View 1 Replies
View Related
Aug 10, 2006
I am testing out Synchronous Mirroring and have a question about failover. It seems the only way I can failover to the Mirror box is to log onto the Principal box and hit the Failover button? Is the whole point of mirroring to be able to handle the Principal not being available? Is there another way?
TIA, ChrisR
View 4 Replies
View Related
Oct 12, 2007
Hi All,
Does anybody know the OS requirements for Database Mirroring? I checked BOL and Google but couldn't find any info on that.
Thanks.
View 5 Replies
View Related
Feb 27, 2008
Hi All,
I have read on the web that high protection mode not recommended, except in the event of replacing the existing witness server. But I can't find the reason why anywhere. Can anybody explain? Thanks.
View 2 Replies
View Related
Mar 12, 2008
Hi All,
Couple of questions about database mirroing.
1. Once the mirroring is setup is it possible to switch between high-protection and high-performance modes? If it is will I have to stop the mirroring, switch the modes and then restart it again?
2. Let's say the principal server went down and I manually failed over to the mirror server. Mirror server runs as a new principal server for a couple of days and then I bring the original principal server back up. What needs to be done in order to bring transactions on the principal server up to date?
Thanks.
View 1 Replies
View Related
Apr 1, 2008
Hi,
I have place an question, but in some other place in the same forum.
My apologies for that
I wanted to implement mirroring
I hereby let u know the steps
1. Created a database in Principal server named Mirror1 and one table named tblMirror1.( no values inside the table)
2. Repeated the same in Mirror in Witness
3. Selected the Principal server's database -->right click-->tasks-->mirror
4. from the left hand panel selected Options -->recovery model-->full
5. selected Mirroring from left hand panel-->configure secuirty-->entered mirror and witness name--Success
6. When I click on start mirroring I have an error stating
" Alter failed for database mirror1"
"Database mirroring cannot be enabled because Mirror1 database may have bulk logged changes that have not been backed up etc...."
7. followed this one
BACKUP database mirror1 TO DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'
got the answer as
Processed 160 pages for database 'mirror1', file 'Mirror1' on file 2.
Processed 1 pages for database 'mirror1', file 'Mirror1_log' on file 2.
BACKUP DATABASE successfully processed 161 pages in 0.318 seconds (4.147 MB/sec).
According to sources
Run a restore of this backup on your mirror.
This can be done through the GUI or using a T-SQL command.
The database restore must use the NO RECOVERY option, so the database stays in a loading state.
Also the database name on the mirror must be the exact same name as the principal
RESTORE TestMirror FROM DISK='C:Backup TestMirror_FULL.BAK' WITH NORECOVERY
Hence did the below
RESTORE database mirror1 FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK' WITH NORECOVERY
ERROR
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
HENCE MODIFIED AS
use master
go
RESTORE database mirror1 FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK' WITH NORECOVERY
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Please let me know what shuld i modify
regards
cmrhema
View 20 Replies
View Related
May 16, 2008
I think I've read some conflicting advice in BOL. Maybe someone can clarify it for me.
Under "Database Mirroring and Database Snapshots" it says:
"You can take advantage of a mirror database that you are maintaining for availability purposes to offload reporting. To use a mirror database for reporting, you can create a database snapshots on the mirror database and direct client connection requests to the most recent snapshot"
To my mind, to enjoy a noteworthy performance gain, that mirror database would need to be on another server.
But then you read under "Database Snapshots":
"Multiple snapshots can exist on a source database and always reside on the same server instance as the database."
So how do you get the snapshot to the mirror?
View 2 Replies
View Related
Jun 5, 2008
I have a quick question regarding mirroring failover. If you have mirroring setup with a witness and the principal server goes down, once the principal is back up as the mirror will it automatically synchronize with the new Principal?
The main thing that I wasn't sure of is that if it took a fair amount of time to bring the original principal back up, the new principal will be in production so will all the new transactions be sent to the mirror once it is available?
View 1 Replies
View Related
Mar 15, 2007
for a database mirroring , which stretergy is good.
Creating with witness server or without it.?
I am firsttime doing this , so i am posting it to the forum..
and also if we are creating the database mirroring with witness server for automatic failover , will the witness server need the same amount of harddisk space like the pricipal or mirror server.??
thanks
View 2 Replies
View Related
Mar 21, 2007
Hi,
We have 2 servers each having a domain (i.e. 2 domains) running
Windows Server 2003
Standard Edition
Service Pack 1
We would like to have one of the servers replicate/mirror the data on to the other but we are getting this error
The error produced from the mirroring part is:
TITLE: Database Properties
------------------------------
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:
Alter failed for Database 'CTSM'. (Microsoft.SqlServer.Smo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server network address "TCP://UM_db_live.UMlive.local:5022" cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)
Thank you.
View 3 Replies
View Related
