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
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
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
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.
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,
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.
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.
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....
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.
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.
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.
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??
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.
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?
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.
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"
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.
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.
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
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?
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.
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?
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.
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."
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?
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.??
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)
