Not sure if this is relevant here but I thought someone might have seen this.
I am trying to set up Express edition as a mirror witness and am running into a problem.
It seems the express instance thinks its "Host Name"[1] is foo-prod-witnes rather than it's real name foo-prod-witness (notice the additional "s")
When I try to create the security endpoints everything appearsd to work as far as connecting to the express instance but i cannot start the mirror because the address listed is
TCP://foo-prod-witnes:5022 (not a valid hostname)
My enterprise editions report foo-prod-sql01.fqdn:5022
Any ideas how to fix this?
1. As shown in the Host Name tab of the Configuration Manager
I have set up a SQL Express sytem to act as a witness....all are under the same admin account, all can ping and see each other. When I attempt to start a mirror session, it fails to issue an "ALTER Database" comment and the mirror setup is set to "High Performance".
This is my first time setting this up and it took over a day to do this as it is a relatively new concept, I am not DBA, just a S/W developer set up with the task of communicating with two DB instances which are mirrored. A couple of questions:
1) Is it normal to see the mirror instance in the following mode: (Mirror, Synchronized/Restoring)?
2) Any special steps required on SQL express besides enabling TCP/IP within the Configuration manager? It doesn't need the same instance of the same databaes on it, does it?
After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.
The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.
ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'
After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.
The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.
Any idea's why the state between Mirror and Witness Connection remains Disconnected?
I have a data file that has numeric data that looks like:
1.123456
And this column is defined as a DT_NUMERIC(18.6) in the flat file conn mgr.
As an experiment, I changed the destination column to a NUMERIC(18,0) - hoping that this would throw a truncation error at the flat file task level (where I have Truncation on all columns set to "fail component").
Not a peep. It loaded the data into the table, chopping off the 6 digits after the decimal point.
You would THINK that this would cause an error, but no. Why is this? The flat file task complains about all kinds of things, but this is such a gross error, you would think it would catch it!
Problem: ======== A few records in a particular table are getting updated with incorrect values everyday somehow by some process. No one is manually changing these records. Every morning I query the database and fix these records. But somehow they again get set to the incorrect values.
So I want to figure out how this is happening. I'm thinking of creating an update trigger on that table. And whenever this update trigger is fired, check the modified record's id and if it is the problematic record, then get the hostname, username, processid which is doing this update.
Can someone give me ideas on how to get the hostname, username.
I want to capture hostname of the client computer making changes to a table on the server. I tried to use sysprocesses table, but the max(last_batch) column is giving the datetime and hostname of previously ran query. I need to find the hostname of the client who is making change at that instant of time.
I have problem with identitys when subscriptions uploads new inserted data, I need somehow create a partitions for each user. All user need exactly the same data (now 10 user but in future about 100 users).
In the database is 116 tables and still growing. Because this is a project i working with.
My system is a one Server, one publication and server is acting as its own distibutor, and multi users
I have really problems with conflicts plz somebody help
I'm having trouble with my Merge Replication setup in regards to changing the HostName property of the SqlCeReplication object. As we all know, if you modify the HostName value, you must then call the ReinitializeSubscription(true) method before calling Synchronize(). Unfortunately, this isn't working for me. Following this sequence of steps consistently results in error messages saying that I need to either call ReinitializeSubscription, or change my HostName back to it's original value. Has anyone ever experienced this problem?
Can someone explain me, if it's possible to set my SQL servername equal to the hostname of the server ? Of the moment, i have to log in with : WS003/sqlexpress But I want to log in like this : WS003
On SQL Server 6.5, we tried to track down the owner of a process,which was hogging the CPU. Using Currently Activity Detail, we went to the hostname that seemed to be the culprit, but the users had not accessed SQL Server in a few days. Where is SQL Server get the hostname from? We are on an NT4.0 network, using DHCP.
I'm trying to get into an installation of v.6.5 to use with anotherserver and I need the following information:Database hostnameDatabase nameDatabase usernaneDatabase passwordIs this information available anyplace within the installation files?I'm not asked for uname and pwd to get into the database.Thanks!
Does anyone know where I can get SQL Server 2005 witness hosting? Our primary server is in Vermont and our DR site is in New Hampshire. I would like to set up a witness server somewhere off the east coast. I don't want to have to purchase a new server and pay big prices for colo.
Is there such a thing as witness hosting? Would someone be willing to host my witness and I can host yours?
We are trying to set up database mirroring. The mirroring works for the principal and mirrored databases (set up via Management Studio), but when we try to set up the witness database, we are getting the error message €œThe ALTER DATABASE command could not be sent to the remote server instance 'TCP://€¦ (Error: 1456)€?. The same occurs if I try to do €œALTER DATABASE xxx SET WITNESS =€? in TSQL. We are using the Standard edition for all three. We have tried using several different servers for the witness. All same user, same domain. Any suggestions? Thanks.
I have managed to set up mirroring on SQl 2005 however i have a come across a problem that i dont know how to resolve.
Im running the mirroring with 'High Safety with automatic failover.'
If i instagate a failover on the Principal Database, the mirror becomes the Principal, and vice versa ... great .. how ever .. i tried the golden test by making the principal server dissapear by yanking out the network connection, expecting the mirror to become the principal.....nope it just stays there in a disconnected / restoring state which isnt any good to man nor beast . I thought that the Witness should have dectected that the principal database had 'dissapeared' and would have made the mirror the Principal.
Any pointers on where i might have gone wrong in the setting up process, i used the mirror wizard
I removed mirroring on a configuration that is currrently working. Next, I moved the witness server to another IP and changed the FQDN (I patched the machine to another newtork, added a FQDN to our DNS server and reconfigured the machines IP information. I can ping the witness at its new address from the principal and the mirror).
Would changing the Witness's IP and FQDN prevent Database Mirroring from being setup after the certificate has been created and distributed to the principal and mirror instances under the old IP and FQDN? Should the endpoint be reconfigured and a new certificate be generated and distributed after an IP and FQDN change?
I start with 3 servers, in High Safety mode with witness. I disconnect the network cable for the witness. Then I remove the witness from the mirroring session:
ALTER DATABASE db1 SET WITNESS OFF
I reconnect the witness network cable. Now I cannot add the witness back to the mirroring session (either with TSQL or thru the GUI). Here is the error message:
Msg 1433, Level 16, State 4, Server SERVERA, Line 1 All three server instances did not remain interconnected for the duration of the ALTER DATABASE SET WITNESS command. There may be no witness associated with the database. Verify the status and when necessary repeat the command.
I have to stop mirroring (SET PARTNER OFF) and then reconfigure a new mirroring session to be able to include the witness.
If I don't disconnect the network cable, I am able to remove and re-add the witness to the session, no problem.
The problem: One server sees the witness, one does not. If the dbs are active on the one that doesn't see the witness and I shut that server down everything works as it should. However, if the dbs are active on the server that sees the witness and I shut that one down the mirror does not take over because it can't see the witness and form a quorum. It throws Event ID 1438 every 20 seconds complaing "The server instance Witness rejected configure request...The reason 1451, and state3, can be used for diagnotics by Microsoft...."
The scenario: I finished setting up a mirror in a WORKGROUP, by going through the GUI and then at the choosing not to start the mirror. That allowed me to modify the NETBIOS names and change them to IP addresses in order to get around the FQDN requirement that I can't fulfill since the servers are in a workgroup. They are in the same subnet and can run SQL queries against each other.
When I query sys.database_mirroring from the database servers I get this on the good server: mirroring_witness_name mirroring_witness_state_desc ---------------------------------------------------------- TCP://155.17.92.63:5022 CONNECTED TCP://155.17.92.63:5022 CONNECTED
And this on the bad server: mirroring_witness_name mirroring_witness_state_desc ----------------------------------------------------- TCP://155.17.92.63:5022 DISCONNECTED TCP://155.17.92.63:5022 DISCONNECTED
here's are the results from sys.database_mirroring_witnesses database_name principal_server_name mirror_server_name safety_level safety_level_desc -------------- -------------------------- ------------------------- ------------ ------------------ dbadmin TCP://pscdaltpscom20:5022 TCP://155.17.92.65:5022 2 FULL dbtest TCP://pscdaltpscom20:5022 TCP://155.17.92.65:5022 2 FULL
Oddly, the server that I created the mirror from registered itself with it's NETBIOS name even though I told it to use an IP address. However, that's the one that is working properly. I think this might be a red herring.
Does anyone have any ideas why my second server can't communicate with the mirror? Any clarifications I can make? I have been completely unable to find a step-by-step guide to creating mirror databases outside of a domain...so if someone knows of one please point me that direction.
my SQL is running TCPIP and everytime I register the server from a client, I have to enter in the IP address. Can I use a hostname to map an IP address to a server name and then enter the server name to register the server?
In SQL2005, is it possible to restrict incoming sql or windows authentication logins by source hostname or IP address? Perhaps using endpoints?
We are currently using hosts file on the db server as a temporary solution, but the SA will not allow us to use this as a permanent solution.
The app team I support is asking me to build an "idiot proof" database so that an Dev or Test app server they may configure, doesn't unintentionally connect to the Production database and damage/alter data.
Don't worry, I've already asked if they can't follow some kind of M&P's to prevent this kind of silly behavior, but they insist on DBA team creating a bullet-proof database that can outsmart app developers who try to attempt something dumb like this.
Any ideas or suggestions would be greatly appreciated.
Do I have to do anything when hostname (servername) with SQL Server 2005 install was changed from OLDNAME to NEWNAME? If yes, what need to be done and syntax? I notice it under SYSSERVERS table still contain the OLDNAME.
In an Insert Into statement I have used the Host_Name() function toidentify which user has suppied a record to a table that holdstemporary data.I'm using an Access2K front end.Code:Alter procedure SPName@parameter1 intASSet nocount OnSet xact_abort offDeclare @myHost nvarchar(50)Set @myHost = Host_Name()Insert Into tblMyNameTEMP(UniqueID, AnyField1, AnyField2,myMachineName)SELECT tblMyName.UniqueID, AnyField1, AnyField2, @myHostFROM tblMyNameWHERE tblMyName.UniqueID = @parameter1I have two problems.In some cases (and only on one or two of maybe about 250 clientworkstations) Host_Name() returns the name of MY machine. I'm thinkingthis is because I developed the app and distributed it and for somereason it's retaining the info contained in my original connectionsetup set in the MS-Access Connection window???Also, on some occasions, I get blocking messages in my trace logfollowing this operation.Any help on these two issues is appreciated.lq
We are attempting to improve our merge replication process between our SQL Server 2005 server and SqlCe Mobile 3 client by switching to Data Partitions. We are using IIS as a proxy to SQL Server 2005 running on a different box using a DOMAIN account.
We've setup row filters to use HOST_NAME() and have set the option "Automatically define a partition and generate a snapshot if needed when a new Subscriber tries to synchronize" to true in the Data Partitions options under the publication's properties in SQL server management studio.
If I use a .HostName value of "1234", everything works fine. A subdirectory is created under a our publication's folder in the shared replication directory that relates to the host_name. Data is copied to the device and can be observed through query analyzer that the data is in fact filtered properly.
However, when using a .HostName based on the GetDeviceUniqueID (which results in '3D321F7212B2AD2CC824954662B9023441BB2D20'), replication works sometimes and sometimes fails with "The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have ben unable to create or write to the message file. [etc]". The final HRESULT is 80045017.
While this error indicates a permission problem, there were no permission problems when creating the "1234" partition. In researching the problem, I ran across KB905395 which states: Limitations on the HostName property and the Subscriber-requested snapshotIf you try to initialize a SQL Server Mobile Edition subscription by using a filtered HostName property, initialization fails. This problem occurs when the value that is supplied for the HostName property contains more than 12 characters. To work around this problem, disable Subscriber-requested snapshot delivery at the Publisher. You can also use a value for the HostName property that contains fewer than 12 characters.
====
This seems to be consistent with what we've observed (which actually led to the "1234" test). Does anyone know if a fix for this exists? Has anyone else run across this issue? I saw a previous post from March that did, but never got resolved. However, the KB article above was posted in March - hmmm...
This is an existing application and filtering based on the device's id is ingrained in the application. Changing it now would be large undertaking.
Regards, Santino Lamberti Senior Software Engineer Launch Technologies, Inc.
I am using Merge replication for synching data between SQL Server 2005 and Mobile units (SQL CE 3.5). I have created one publication and there are around 300 subscribers in the production.
If I want to use a HostName() filter so that each user gets his or her own data, do I have to set up 300 different subscriptions with different HOSTNAME values or can I use the same subscription somehow?
I would appreciate if someone can let me know whats the best way to approach this problem.
Not sure where I am going wrong ... I am able to set-up the mirroring, but the witness set-up always fails. I am running all the 3 SQL servers (Enterprise edition) with same domain account, which is also a member of sysadmin locally within each SQL server.
When I try to add the witness on Principal using the following command:
ALTER DATABASE MyDB
SET WITNESS = 'TCP://WitnessServer:7022'
I have even tried the IP address with port number and fully qualifying domain (TCP://WitnessServer.xx.xx.com:7022) and I get the following message in error log.
2006-06-20 10:16:55.07 spid57 The ALTER DATABASE command could not be sent to the remote server instance 'TCP://WitnessServer:7022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
All the 3 servers are configured to use TCP and Names pipes. I can connect, ping and telenet the Mirror Server from Principal. Any suggestions?
I'm running into a problem where I had mirroring working correctly (on server A ->B with C being the witness) then I set another machine(s) up to also use mirroring (X->Y with C also the witness). When I checked the A->B configuration today the witness was disconnected so I removed mirroring all together and attempted to set it up and I'm now getting the error:
TITLE: Microsoft SQL Server Management Studio ------------------------------
Alter failed for Database 'A'. (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 ALTER DATABASE command could not be sent to the remote server instance 'TCP://C:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again. (Microsoft SQL Server, Error: 1456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1456&LinkId=20476
Hi, Can anyone help, how to find client's hostname using RDP from MSSQL command. In simple words, I am connecting to Remote desktop and using MSSQL server management studio in the remote desktop. And when I run select host_name(), it returns the RDP machine name. However, I wish to see my own computer name. Thanks in advance. susuown
Sadly the hostnames for the boxes I am trying to replicate are similar:
- db1.nyc.mydomain.tld - db1.sac.mydomain.tld
The servers can talk to each other over all necessary ports however when I generate a push subscription from the publisher, I am asked to add the SQL Server subscriber and it already shows DB1 listed since itself is DB1.nyc. How can I attach the remote subscriber when setting up the subscription from the publisher if the hostname conflicts in my unique scenario?I created a entry on the publishers host file to use db2sac for the SAC IP and entering that alias in as the remote subscriber but no dice.
When using the MS SQL 2005 JDBC driver, I now need to have the DNS nameresolution to the client correctly set up. If not I get the followingerror:SQLState: 08S01SQLError: 0Message: hostname : hostnameWhere "hostname" is the client host name. It doesn't seem to matter ifIuse an IP address or hostname in my connection URL. When searching thenet,I have seen a similar error, but not exactly the same, where themessage is"Hostname: hostname not found" (once again, here "hostname" is theclientmachine) where they suggested the issue was the DNS name conflict.I found the error goes away if the client machine's hostname match theDNSname used for the same IP addess that the DB server is using. Onework-around is to set hostname on the client to the IP address beingused.I know the right thing to do is set the hostname and the DNS namelookup thesame, but I have a customer who doesn't want to do this. They didn'thave tofor 2003, and they don't want to for 2005. Sigh. Is there a way todisablethis feature?
