Hello all,
Many people are angry at MS for promising and not delivering on the mirroring feature of SQL 2005.
Does anyone have any idea - officially or unofficially when this feature will be available for reliable use, as we would like to use it.
Thanks,
S.
dear all,
I have a case.
I have 1 production server and wanto to add 1 more server.
my production server is using sql 2000 stnd edition.
I want to have an indentical database from my production server.
the problem is my second server is using sql 2005 stnd edition.
I have try to use replication but because the my production database didn't have relationship on the database so I can't use transaction replication, I can't use mirroring and log ship too because the sql use the different version.
any idea or suggestion will be great....
regards,
-dedys
I've a SSRS report with the details in regards to shipping information. Currently in my report the 'Scheduled Ship Date' is set to following Monday, if we miss the 'Requested Delivery Date'. However, I need to change this report to set shipment date('Scheduled Ship Date') to Tuesday and Friday for the shipment having 'Requested Delivery Date' for this week or next week(current week and next week).
For example- Jobs that have shipment date between 3/1/2015 - 3/7/2015 will have 'Scheduled Ship Date' as Tuesday and Friday of current week. If we miss the shipment for this week it should be scheduled for next Tuesday(3/9/2015) and Friday (3/13/2015). I need to follow this for current week(3/1/2015-3/8/2015) and following week(3/9/2015-3/15/2015) and after that all the following weeks will have 'Scheduled Ship Date' as Tuesdays.
Below is the SQL:
USE tempdb;
GO
DECLARE @Date datetime;
SET @Date = GETDATE();
--SET @Date = '2014-07-25';
DECLARE @TEST_DATA TABLE
[code]....
I've a SSRS report that reports the 'ScheduledShipDate' based on 'Requested Delivery date'. Currently, on my report 'ScheduledShipDate' is set to Monday of each week since we ship every mondays. I need to change the 'ScheduledShipDate' since from now the ship date will be Wednesday, Thursday and Friday of each week. Here is my DDL.
USE tempdb;
GO
DECLARE @Date datetime;
SET @Date = GETDATE();
--SET @Date = '2014-07-25';
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
[Code] ....
hi, If I want to make one to many relationship, I put the pk of the parent as a fk in the child . like this
customer table (cust_id, fname,lname)
customer_order table(order_id,cust_id,qty,price)
I do understant this, but what about look up table, can I consider cust_id inthe customer_order table as a lookup field to the customer table?
Thanks
Al
I have new client we changed the file retention period on for log ship on SQL 2000. Now the files are not being deleted. I looked at all the sp's but could not finde the one that actually deletes the log file that was copied over. I could just rebuild log ship but this is on a prod box, was trying to figure out what and when the files get deleted, and by what sp
View 2 Replies View RelatedThis one is a needle in the haystack. I have a fairly robust Dell server running WIn 2003, 16 gig of memory, SQL SP3a using log shipping failing over to a local server and then back to my location across a WAN, using a vb6 app with about 60 users, a 24X7 shop Mon-Sat. 2 times in the past month the production server has created a corrupt log ship file which gets applied to my failover server and then corrupts the failover DB to both failover servers. Of course it is my 20 gig db and the only time I can recreate the failover is Saturday night. The production DB is not corrupt and is fine. Has anyone ever run into this before. Currently working with MS and Dell, but hoping someone had experience with this !
View 2 Replies View RelatedI am trying mirroring in my own laptop in 2 different instances of 2005 but when i configure it gives me an error "one or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click start mirroring again"
does anyone know what is the problem???? i read one post but could not understand properly.
Narayan
i setup sql 2005 mirroring
when i run the wizard and start mirroing i get the folllowing error:1418
in the log it says database mirroring login attempted by user NT AUTHORITY/ANONYMOUS logon does not have connect permissions on the endpoint
my service acount is LOCAL SYSTEM
all 3 sql servers are in same workgroup
i can connect with studio to all 3 with no problem
any one have any clues here
Hi everyone,
I have a project on my hand in which i need to implement two SQL 2005 servers
And configure it as DB mirroring.
I know that for the sake of 'fail over' I need to configure a witness
server as well.
My question is:
For the purpose of the mirror server and the principle server - what
are the software requirements (enterprise, standard?)
For the purpose of the witness server - what are the software
requirements (enterprise, standard, express?)
Should the witness server be a 'server' in terms of hardware...or is
it enough to establish a strong pc machine?
Hi,
I tried many times checking "Save password" in connection manager editor. But everytime I open the editor again and I see the password field blank.
I took it as normal, and deployed my IS Package, It couldnt run because of login failed for user 'sa'.
How do I force IS to save password on any ProtectionLevel ?
Thanks,
Fahad
Good day to the people who's reading this post!
I have some trouble with shipping my transaction logs to the secondary database be it on
another server or within the same server to another database instance.
Im using SQL Server 2005 workGroup editions with Service Pack 2.
Here are the problems that i encountered.
I do hope someone has bumped into such a problem and willing to help me out.
I tried on 2 separate servers (not domain environment) and also between 2 separate instances
(which is supposed to be simple!) on
our development server,but was unsuccessful.
Between 2 separate instances on the development server -
No error after configuring Log shipping though.
The configuring went through and it was a success.
Transaction logs was backup every minute and it got copied over to the other folder.
But SQL Agent not doing its last job which is supposed to restore to the secondary database on
another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to
look to see if there are errors given out?
Both instances SQL Agent has the same log on username and password with Administartive rights
So what went wrong?
Between the 2 servers-
The transaction logs was backup every 1min on the primary server
but it didn't got copied over to to the secondary database.
- Error message given was:Error in restoring database to the secondary
database.Network path given could not be found. Can't open the AxTest.bak file.
(i am very sure i have type the correct network path,even have shared it out and i think the firewall
is blocking incoming traffic since unlike our development server,
which allows us to access when we use Start>Run and type in the ip address,user name
and password,the primary server will only tell me no network path was found)
I also believe it's because the SQL Agent on the secondary database server wasn't given
permission to access the primary database folder.
I've shared out the drive and folder on the secondary server and
even have allowed SQL Agent to read,write and modify on both servers.
For the primary and secondary SQL Agent,
I configure their log on to be the same user account name and password
which have administrative rights.
So what went wrong?
Isit really true that both servers have to be in domain environment before you can configure log
shipping,mirroring and replication?
Hope someone help me out of this predicament.Thank you in advance!
Hi guys,
I have read many articles on the matter and I have probably used up all my printer's ink in doing so, however, some questions still remain.
1) What happens if I have to reboot the mirror.. security update, etc.? Obviously the session is broken during reboot, but would I have to do another backup of the principal and resync everything?
2) I know it is not best practice but at this point I have no choice, however, I wanted to get your guys feedback on having two instances of SQL2005 on my development box. One for the mirroring of the production and the second for development. The two instances would live on their own drive... not partitioned and have adequate memory and space.
What would I have to look forward with this?
3) Lastly, I am still uncertain if mirroring is approved for production. Is it?
Thanks for your help.
I am working on a proof of concept to take to our Technical Board.
This POC is concearned with SQL server 2005 Mirroring for High Availability and Failover in a Highly Critical business sector.
The problem is that we use custom web pages to give the Operations Center an overview of the status of the system and that there are so many metrics available (not may are well documented). I have looked at the following
sys.database_mirroring_witnesses
is_suspended to show the state of the Sync
database_name to return the name of the mirrored database
safety_level_desc to show that the Safety level is at Full
Principal_server_name to show the name of the Principal server
Mirror_server_name to Show the Name of the Mirror Server.
I am at a loss as to what is the best approach as I do not want to display too much Information and confuse.
The Servers are locked down to remove any permissions not required so I have to create a SQL account with only acces to the required sys views. one issue is that the following SQL only works on the Principal
Code SnippetSelect * from sys.database_mirroring where mirroring_guid is not null
The Principal returns
Code Snippet
7 267CF8D3-8066-44EA-84BD-B7EA25C3F779 4 SYNCHRONIZED 2 MIRROR 20 2 FULL 1 TCP://DNGLHRSQL051.XX.XX.XXX:5022 DNGLHRSQL051 TCP://LHRSQL029.XX.XXX.XXX:5023 1 CONNECTED 40000000010300001 10 NULL UNLIMITED
where the Mirror will return nothing..
The configuration of the User is identical on both Principal and Mirror and is as follows
Master Public
MSDB Public, dbm_monitor
I have read that the User also requires "view any Database" but on the Mirror this database is not available on the Mirror as it is in Sync.
Is this an undocumented Feature?
Any help greatly appreciated
Regards
Andy
I am trying to set up a SQL mirroring senario - I have some questions
Do I need to purchase an extra SQL Server license for the SQL server instance on the Mirror? what about the Witness does that also need a separate license/
Can the witness be a virtual server on the Mirror box itself? Do I still require a license if the witness is on a virtual server?
Thanks
jawahar
Hi all,
I am using SQL 2005 (Enterprise Edition, SP2) Mirroring with a Principal, Mirror and Witness set to High Safety mode and am a little confused!
As far as I understand it, during a failover, although the database content will automatically be available on the Mirror any user accounts will need to be either manually created or manually mapped to the new principal. Is this the case? If so, I can't really see the purpose of using Mirroring (in my scenario anyway).
For example - We principally use the database servers for our web applications which we connect to using ADO.NET. Each database and Web app has a different SQL user account to allow access etc. If we experience a failover this means the users will not be able to use the web app until we have manually recreated/mapped the SQL accounts for each and every database. Surely this is a massive oversight?! Or am I simply getting the wrong end of the stick.
Any help/guidance would be greatly appreciated!
Thanks,
Richard
Hi,
If I perform a point in time restore on a database that is currently a primary log shipping database, will the rollback be reflected on the secondary servers or are extra steps neccesary to accomplish this?
Thanks.
I'm devlving into 2005 and specifically mirroring.Having read the following article (http://www.microsoft.com/technet/pr...5/dbmirror.mspx ),is mirroring a sensible option as it indicates that it should not beused in a production environment, yet the SQL2005 seminars I've been toindicate that this is a worthy option.Do we also need a witness server, or can this be done (sensibly) withtwo SQL2005 servers ?Currently we have 2xSQL2000 (clustered) servers and a SAN for the data.If we go down the route of mirroring, it looks like we can get rid ofthe SAN and Cluster and use the mirroring instead.Any advice on the pro's / cons would be appreciated.ThanksRyan
View 5 Replies View RelatedCan I know is it possible using database mirroring for only certain tables from the database, not for the whole database in sql server 2005. Hopefully able to get any assistance here. Thx alot.
Best Regards,
Hans
Hi, for SQL 2005 - to set up database mirroring, is it required to have Active Directory installed or to be part of a domain? I am looking to use a workgroup with the SQL machines only and to not reference any domain and cannot find this information - is this possible?
View 5 Replies View RelatedHi, im trying to mirror a database with SQL server 2005. I have completed all the setup for this, including attacthing a copy of the principal database and all the other settings. When I completed the Mirroring wizard, it prompted me to start mirroring and when I accepted, it gave to me the following error:
TITLE: Database Properties
------------------------------
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:
Alter failed for Database 'CTM'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server network address "TCP://DBNAME.domain1.local: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. (Microsoft SQL Server, Error: 1418)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476
Can anyone help me withthe problem.
We have a .NET application with a backend SQL 2005. The issue started when we setup DB mirroring on this db. The issue is an System.InvalidOperationException that is thrown every once in a while when connecting to the databases. The message is 'Internal .Net Framework Data Provider error 6.'.
Both Operating System (Windows 2003) and databases (SQL server 2005) are up to date, including all Security Updates and Service Packs.
The issue disapeared when we stopped db mirroring.
Any one has this issue?
Thanjs
Ayad
I am new to 2005 mirroring but have read a lot of documentation on it. I want to use mirroring to centralize a bunch of DBs to one location and then back them up from that central point. However, from what I've read since the mirror is kept in a "recovering state" it cannot be accessed directly. Does that mean I wont be able to backup the mirrors? If so, are there any work arounds or different strategies for this?
Thanks!
Craig
Hi....
I have sql server 2005 evaluation edition. And This version doesn't provides Mirroring option.Even after installation of service Pack1. Is there any other edition (Freeware or Evaluation) which provides the Database Mirroring.
waiting for the prompt reply.
Thanks
Can I use Database mirroring between 2005 Std and 2005 Ent editions?
View 2 Replies View RelatedHi, iam from Mexico,
somebody has installed SQL 2005 with mirroring and sharepoint 2007?
regards.
thanks
I was wondering how I do to insert values in two tables that are related each other by a FK?
That is the procedure that illustrate what I meant to be.
ALTER Procedure [dbo].[new_user]
@master nchar(10),
@nick nchar(10),
@fish nchar(10),
@e_mail nchar(30)
As
Begin
INSERT INTO users
(nick, fish, e_mail)
VALUES (@nick,@fish,@e_mail)
INSERT INTO friends
(user_id, e_mail)
VALUES ( Select user_id from users where nick=@master,@e_mail)
End
Thank you very much.
If I wanted to make a report that shows different fields, one being cost, but I only wanted certain people to see cost based on their AD groups, is there a way to hide this information?
Basically, domain/user1 belongs to AD purchasing group purchasing, he can see item, description and cost, where domain/user2 belongs to sales so he can only see item and description?
I want to build Parent child relation . i have two aproaches .I would like to know which is the best solution ?
1)1st method:-
Parent table with parent id . Child table with child id and parent id.Foreign key relationship exists between parent and child tables with cascade delete option enabled.
Parent TAble
Id name
1 XYZ
Child table
id name parent id
1 abc 1
2 qwe 1
2)2nd method
table with id and parent ID. Top level element will have null value in table. eg
id name parent id
1 xyz
2 abc 1
3 qwe 2
4 adf 1
Retrieve data using recursive queries supported in SQL Express.
Which is the best solution to store parent child relationship???
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?
Hello, My question is simply: according to BOL, a DB snapshot taken from a Mirror is read-only. Say I wanted a temporary (disposable) read-write DB created from the snapshot. Is there a way to generate a writable temp DB (that's not tempdb) from the snapshot? Thanks,AK
View 1 Replies View Related I am developing an enterprise class solution using SQL Server 2005 and MS .NET v2 and am tying determine if SQL Server 2005 (which edition and if so how) would be adequate for my proposed solution. Any feedback, tips, comments would be greatly appreciated.
As a background the solution I am developing will be web services based and used by multiple offices around the globe by over 500 users. I have already developed a prototype using a single SQL Server 2005 instance but as this solution is going to be used by offices around the world I want to have an IIS Server and SQL Server 2005 server instance in each office with "links" back to the primary SQL Server 2005 cluster in Australia.
One of my thoughts was to set up replication between the offices that would happen at midnight remote office local time and then set up triggers to update the primary cluster when assoociated data was changed on the remote sites or on the primary cluster. Does anyone know or can anyone suggest alternatives to this strategy?
I effectively need some sort of inter site caching functionality with store and foreward capabilities ...
Thanks
Andrew
Hi,
I was hoping to learn from real world experience regarding the hardware requirements for SQL 2005. For both the 32 and 64 bit OS the minimumn recommendaiton is single processor, 1GB or more, 512 MB RAM.
http://www.microsoft.com/sql/editions/enterprise/sysreqs.mspx
This seems very thin and I was wondering if anyone had recommendations for hardware configs for a mirrored db cluster? I'm filling in as a DBA and apologize for the ignorance.
Jody