I am struggling with configuring SSB network routes to a mirror database. What I want to accomplish is to configure a SSB application to work in a database mirroring setup but I am not looking for a load balancing solution.
According to SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms166090.aspx) the typical routing configuration for a service hosted by a mirrored database is set by specifying the 'mirror_address' field but leaving the 'broker_instance' field empty (i.e. NULL) in the sys.routes table (see Example 3 in link above). I haven't seen how this is possible because if you specify the MIRROR_ADDRESS parameter in the CREATE/ALTER ROUTE command then you must also specify the BROKER_INSTANCE parameter.
I haven't found a way to set the mirror_address field to a valid value and the broker_instance field as NULL, is this possible? If not I must set the broker_instance as the guid of my broker instance but my understanding is that it should primarily be used for load balancing configuration. My preference would be to not set the BROKER_INSTANCE parameter, is this possible?
I'm having trouble trying to access a network share that comes via a UNIX server running SAMBA. In the first case, I'm running on my local workstation (A), connected to a remote server (B), and attempting to access directory information for a path like:
\a0amsimmsworkseaborg argets11as2981
This path is fully accessible by me from the workstation (A) and the server (B). The files and directories below "work" in the above path are also wide open on the UNIX side (meaning r-xr-xrwx permissions). However, if I attempt to do something like this:
at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)
at System.IO.Directory.GetFiles(String path)
at StoredProcedures.mdcinfo(Int32 sim_id, String mdc_base)
CWD is: C:WINDOWSsystem32
User is: amsimms
Initial is: dbserver
Interestingly, if I run the procedure directly on the server (B), I do not get the exception. So this seems to be more of a delegation problem. The server B's sql server instance is running as a domain account (dbserver), which has been enabled for delegation and an spn has been set up. Is there something beyond this either with the impersonate or delegation configuration that I need to do in order for this to work?
We have a Prinicipal, a Mirror and a Witness server. We have automatic failover configured between the Prinicipal and Mirrored server. When we stop MMSQL service on Prinicipal, not all the databases failover to the Mirrored instance. Any suggestions would be welcomed as we have a tight deadline to get this in Production.
I'm having a problem with SQL2005 Database mirroring.
I have an ASP application that loops for a certain amount of interations and in each iteration I create a SQL Connection object and use the failover partner in the connection string. The object then writes a simple record and then the connection is closed and the process starts again.
About half way through my loop I force a failover to the server mentioned as the failover partner in the connection string. At this point my application encounters a SQL Exception error and the application fails.
I read in this article, http://msdn2.microsoft.com/en-us/library/ms366199.aspx, that this is expected and that you shoud request a new connection using the same connection string but this isn't working for me and unfortunately no examples are given anywhere.
I would appreciate any help. Thanks
Luis Bonilla
Here is a sample of my code:
Dim k As Integer = 1 Dim constring As String = "Server=SVR01.XXX.XXX.comInstanceName;Failover Partner=SVR02.XXX.XXX.comInstanceName;" _ & "Database=test;" _ & "Integrated Security=SSPI;"
Do Until k = 60 Using con As New SqlConnection(constring)
Dim cmd As New SqlCommand()
Try con.Open() '<--------This is where the exception occurs Catch ex As SqlException Dim en As String = ex.ErrorCode Dim em As String = ex.Message Dim emm As String = ex.Number Dim enn As Integer = ex.State End Try
Simple question, I hope. I need to add a column to a table of a database that is mirrored. How do I have to do that? Do I need to stop mirroring? Is it sufficient to simply pause mirroring? If I make the change on the principal db, what do I need to do the make the same change on the mirror?
I have a mirred database and have snapshot created from the mirroed database. I can do data selecting from the snapshot. But when the pricipal server is down , I can re-active the mirroed database.( I did succesfully upto this). Then I need to restore the database from snapshot and which failing with following error message.
Code Snippet Msg 5123, Level 16, State 1, Line 3 CREATE FILE encountered operating system error 3(error not found) while attempting to open or create the physical file 'E:sql_datadevitst_mirrorlog st_mirror_log.ldf'. Msg 5024, Level 16, State 2, Line 3 No entry found for the primary log file in sysfiles1. Could not rebuild the log. Msg 5028, Level 16, State 2, Line 3 The system could not activate enough of the database to rebuild the log. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally.
Sorry if this has been posted elsewhere, etc., please point me in the right direction if it has 'cos I couldn't find it!
Right, we have a mirrored database with full safety and a witness for automatic fail over, all works fine, very impressed with it. Now I need to backup the database involved and this is where I could do with some help and answers and/or tips.
As the mirror database is off-line/recovering it seems you can't back that one up, but I'd like to have something that tries to back it up for if/when it fails over and becomes the primary. The solution I've used for now is to write a small .Net application that uses the client side fail over connection string (Data Source=Server1;Failover Partner=Server2) so that it connects to whatever system is currently the primary, and then issues the relevant 'BACKUP xxx' statements to backup the database.
This applications is launched from a windows scheduled task job on the hour (or near to it), every hour. At 06:00 it does a full backup, at 12:00, 18:00 and 00:00 it does a differential backup, and all other times it does a transaction log backup.
This all seems to work fine so far, and generates all the relevant backup files to a share on another server.
So, my main question is; does this look like a good plan? Am I missing some really simple wizard or button that would backup the relevant database from whatever server is up?
Secondly, are the backups from each server interchangeable as they're in a mirrored configuration? That is, for example, the backup application (on the hour) connects to Server1 does a full back up, followed later by a log backup. Server1 then dies, so the next time the backup application runs it connects to Server2 and because of the current time does a log backup from Server2. If we had to restore from backups, could we use the full backup and log from Server1, followed by the log from Server2? Otherwise I'll guess I'll need to modify the logic for the backup application to detect it's failed over, and maybe do a full backup on Server2, or something like that.
I am a C++/C# developer my SQL skills are very limited. I have a database set up for mirroring and I would like to get an e-mail notification whenever an Automatic Failover occurs. Can anyone show me how to do this using SQLServer 2005? Please provide T-SQL script sample if possible!
I have a database I need to copy from a Prod server to a Dev server. There is not enough space on the Dev server. In looking at the size of the files on the Prod server, the Initial Size property for the transaction log on the Prod server is set to 100,000 MB though the log is using nowhere near that.
This is a mirrored database so the recovery model is "full". I know that to change the initial log size, I have to put the database in 'simple" recovery model. Is this possible? Can I just:
1. Pause the mirror 2. Switch recovery model to simple 3. Change the initial size property to something smaller. 4. Shrink the transaction log 5. Change the recovery model back to full and resume the mirror?
I honestly don't know if the transaction log is needed on the Dev server. Meaning I may just be able to restore the transaction log to a different location on the server and delete it so that new one is created.
Every once in a while a scheduled restore of a production database backup to a development server will fail with the following error.
RESTORE cannot operate on database 'XXX' because it is configured for database mirroring or has joined an availability group
While it is true the production database is involved in database mirroring, the development server does not have database mirroring enabled. This error tells me something within the backup is telling the development server the database is configured for database mirroring.
However the perplexing part for me is that we only receive this error maybe 5% of the time, if that, and only on a couple of our databases. We have numerous other restores of mirrored production databases to development servers that have never produced this error. So my question is what is causing this error to occur, and why is it not happening all of the time? We get around this error by deleting the DEV database and re-running the restore job.
I need to connect my Local Database from SQL Server to the Web Database. I need to know what all things to be done for completing this process. Some one please please help me with this.
Ad if this information is not enough, please do ask me any question regarding this and iam ready to answer them.
One more thing, iam not that familiar with SQL Server 2000 but i can manage to do things.
I am learning dotnet thru Microsoft Official Curriculum and I got stuck up at one point. That is I couldn't configure the required databases according to the sql server I have installed and configured. I created a doctors database and while configuring using the below code I couldn't know what is 'ASPNET' in "SELECT @s = @@servername + 'ASPNET'" Also what is 'webuser'
Hello, I have specified a default database in my web.config like this: <dataConfiguration defaultDatabase="scsLocal"/> <connectionStrings> <add name="scsLocal" connectionString="server=DRLSWARTEBRV;database=SCS;uid=******;password=******;" providerName="System.Data.SqlClient"/> </connectionStrings> Now I would like to configure a SQLDataSource control to use this default database, yet it prompts me for a connectionstring. I know I can program it in the code behind file (if I ommit connection string, it will use the default), but then I can make no use of the wizard for configuring the SQLDataSource which would safe me a lot of coding. Is there a way to configure the SQLDataSource to use the default database that is specified in the web.config? Thanks! Veerle
Hello everyone, I have created my web application in VS 2005 using asp.net 2.0 and the built in sql .mdf database creater that comes with visual studio. After I developed my website, I then wanted to move it to one of our servers on the network, which I did without a problem. The website works fine but here is my challange. I need users to be able to connect to the database via login script and the script inserts computer data into the database. So I figured I need to attach the Database to SQL Server 2005. When I try to do this it gives me this database is in use. So I stop the website in IIS, and it allows me to add the database into the SQL Server. Once I do this, and start the website again, the website no longer works and I get a bunch of database errors, that do not get fixed untill I re-copy the website to the server. Is there a way to just connect to a .mdf file on the network that is not attached to a sql server, through a connection string in a login script? Maybe there is something else I need to do to the database before copying to the server? Or should I re-programm the website with a newly created database in SQL Server, and use that as the datasource?
I didn't know exactly which forum to post this in, so if I need to post it into another one, please let me know. Also, I have absolutely no SQL or DB experience. I am learning as I go.
The Situation: I created a virtual network for my company which included MS CRM, which uses SQL Server 2005. Everything was working fine until today. My company asked me to make a change to the domain name, as we are changing company names. I followed the processes through microsoft's documentation that I found online. The rename went fine. Since then, we can no longer access CRM. When I run the environment checks, it gets hung up on the SQL Server components. It says that it can't confirm the MSSQLSERVER service is running (it is) and it can't connect to the database. As best as I can tell, the database is looking for the old domain name, which doesn't exist. How do I edit or configure the existing database to reflect the new domain name. It's important to do this as we have data in that database that we need to access. Any help in this matter is very much appreciated!!!
Alter failed for Database 'databasename'. (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://Mirrorserver.domainname.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
I am trying to configure a connection manager for a database that is still using SQL Server 6.5 But it is not working properly. I put the server name, user name and pass word, I select the Native OLE DBMicrosoft OLE DB Provider for SQL Server as my provider.
When I click Test Connection it says the connection is fine but then the drop down list for the list of databse names hows nothing!
I have been trying to configure SQL Server 2005 Report Services. Every time I create the ReportServer database, the systems asks me if I want to "update" the database. It allows me no other option. When I click "apply", I get the message that the database version does not match my installation version. Does anybody have any insight into what happening with the configuration? What's more, do you have a solution?
I have created profiles in my database as mentioned below. now i want to delete the profile created in database. can some one tell me how to proceed furthur.
i need to delete them permenantly. EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'MyMailAccount', @description = 'Mail account for Database Mail', @email_address = 'makclaire@optonline.net', @display_name = 'MyAccount', @username='makclaire@optonline.net', @password='abc123', @mailserver_name = 'mail.optonline.net' ----------------------------------------------------- EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'MyMailProfile', @description = 'Profile used for database mail' --------------------------------------------------------EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'MyMailProfile', @account_name = 'MyMailAccount', @sequence_number = 1
- Microsoft SQL Server 2005 Standard Edition running on Microsoft Windows Server 2003 Standard Edition - Client Service for Netware installed. - Access 2000 database in a Novell Directory Services (NDS) Environment - SQL Server service running under local Administrator account.
Linked server configured as follow:
Linked server: MyDB Provider: Microsoft Jet 4.0 OLE DB Provider Product name: Access 2000 Data Source: UNC path to my Access database
Security: Admin with no password (default Access behaviour)
I can connect to my access database by following the next steps:
- Open Management studio on the SQL Server itself (where I'm logged in as Administrator) - Connect using Windows Authentication (Administrator) - Create a new query, run EXEC sp_tables_ex 'MyDB'
This will return all the tables from the Access database.
I can also make a view pointing to MyDB...MyTable.
Enough prove to me It can work....
So far so good
But now the tricky part.
When I login as 'sa', I can't connect and will receive the following message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyDB" returned message "'Full UNC to my access database' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyDB".
(In real life I want to connect to my SQL 2005 Server from a ASP.NET (2.0) application using SQL authentication, but as long as I can't get the above working this is not important for now)
I need some clarification about adding file in to mirrored dataabse in primary server without downtime and breaking the mirror server.
In our environment we are using monutdisks in both the servers. in primary for ex we have F drive for data files under mount disk 3 in mirror server also we have same drive but in mount drive2.
As per my knowledge if it is same drives we can add the ndf files in the primary that will reflect on mirror. but in current situation i am confusing about mount points with different names.
I created the db with the attached script and I am able to access ituntil I reboot the server. I've tried enabling flag 1807 via the SQLserver service and the startup parameters of the instance. In allcases the database always come up suspect after a reboot. There wasone instance where I was able to recover, but I am not sure how thathappened.Does anyone have an idea of how I can reboot the server without thedatabase becomming suspect?USE MASTERGODBCC TRACEON(1807)GO--DBCC TRACEOFF(1807)--DBCC TRACESTATUS(1807)GOCREATE DATABASE ReadyNAS ON( NAME = ReadyNAS_Data,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Data.mdf',SIZE = 100MB,MAXSIZE = 20GB,FILEGROWTH = 20MB)LOG ON ( NAME = ReadyNAS_Log,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Log.ldf',SIZE = 20MB,MAXSIZE = 100MB,FILEGROWTH = 10MB)
Scenario: In general user will take database backup from database server to local system. User perform some changes to his local database based on his requirement. Again user connects to database server and click on update, local database should update to server database by informing each record status to the user. ex: record 1 updated / call closed do you want to update?
Here local database is going to be Pocket pc database and server database is on SQLDatabase. Can any one suggest best way to implement this feature in Windows Pocket pc, c#. I would appreciate your reply.
We have a Windows 2003 Web Edition server serving a site through IIS. It connects to a Windows 2003 Standard Edition server running SQL 2000 SP3a.
This site receives the following error as seen in the topic: [DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.
We've configured the server network utility to only allow TCP/IP connections. The connection string for the site is as follows:
Any ideas or tips on solving this issue? We've noticed it is due to larger queries as smaller ones do work with no problems.
The servers are behind a BSD box running iptables, has 1433,1434 along with standard web ports wide open. Anything outbound is allowed.
Some further testing via ODBC on the IIS server improved things. No more connection pooling for the SQL Server driver allows for 1/3 of the query to run. Still 2/3s of it doesn't show up, and that general network error message appears.
Hi While running a DTS, many times the error message: "[DBNETLIB][ConnetionWrite (WrapperWrite()).]General network error. Check your network documentation." appears. Does somebody knows why?
I am running the DTS from a computer (not the server where the DTS is stored) and it is connected without any problem when this happens.
I tying to test my backup to a network drive but it fails.
Please help ..thanks this is the script -- example from BOL:
BACKUP DATABASE pubs TO DISK '\radrisMSSQLBACKUPpubtest.bak'
this is the error:
Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device '\radrisMSSQLBACKUPpubtest.bak'. Device error or device off-line. See the SQL Server error log for more details. Server: Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.
Every time to restore database, I copy the database backup file to local hard drive. Is is possible to restore database over network? Is other tools need to install? Thanks
Hi,its newbie question.I have one production server(A) ,one backup server(B),additionalserver(C).my database backup are moved from A to C in regular basis.My problem is that when ever I have to restore my database in BackupServer i.e B,I have to first copy that particular Backup File on to my Backup Serverfrom Cthen i have to perform my restoration process.Even if I mapped the drive in which my bakup file is lying ,I amnot able to see that drive in SQL Entriprise Manager and when i amrestoring through Query Analyzerit shows the following error -Server: Msg 3201, Level 16, State 2, Line 1Cannot open backup device 'G:pharmacypharmacy_db_200505210330.BAK'.Device error or device off-line. See the SQL Server error log for moredetails.Server: Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.What i think is it a waste a time ,first copy the bak file in localdrive and perform the restoration process.SO please can any one guide me how should i do the network restorationif possible or put me to right path for doing it so.I am using SQL SERVER 2000,OS WINDOWS 2000 Adv SERVER.cheersT.V
