i have two computers with Sql server 2000 std edition(SP4) and windows 2003 std edition(SP1).
how should i do to balance the loading of sql server and sync these two servers?
The sql server mirroring means that if one server is down, then the other one will replace it?
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
Hi Guyz
it is taken from SQL2K5 SP2 readme.txt. Anyone have idea what to do to implement this ?
Our sp2 is failing. we suspect the above problem and researching it.we are running on default instance of SQL2K5 on win2003 ent sp2
"When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:
Explicitly denying the ALTER DATABASE statement.
A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.
If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP2, you must disable these restrictions and then re-run Setup."
thanks in advance.
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?
hi,
i have developed an web-enabled database application using ASp.net, C# and sql server 2000.
now i want to implement transaction controls over the same
can anyone plz help me in implementing the same?
thanks in advance
Hi,
I have created a ASP.net website and i have a page called Investment choices where the user can either type in the data in a text box or there they can select them from a grid view(called Funds). The Typed data or the selected data goes in to another datagrid(called plans). and i want to implement a display order.
right now when the user adds 6 numbers using the text box and since i seperate them using commas. In Plans datagrid i get the numbers from 1-6. then after words if the user selects the from the Funds gridview it just its adds a number 1.
So how can i have the display order sequentially so if the user types 6 no's it should be 1- 6 and if he selects 2 funds from the gridview it should be 7 and 8. I have a field in the database which is called display order which is a integer. so do u think it makes sense as making that field as an identity field or how should i deal with it.
Any ideas will be appreciated.
Regards,
Karen
I need to perform some operations that seem to require multiple nested tables.
Is the following code the way I should be attempting this or is there a better way?
Here is the sample code:
SELECT tt.tdl_ID, tt.tx_ID, adj.Prof_Chgs, rc.Rvu_Comp
FROM Table1 as tt
LEFT JOIN (
SELECT tt1.Tdl_Id,
CASE
WHEN tt1.tdl_ID = '1000'
[code]....
Hello,
I want to set up As400 DB as a publisher and the sql server 2005 as a subscriper, so that I can read some data from AS400 DB in sql server 2005 and every evening, I need the replicated data from AS400 to be refreshed.
Can I do it ? If yes, how to do it?
Thanks
We need to implement incremental load in database. A sample scenario is, there is a view (INCOMEVW) which is build on top of a query like
CREATE VIEW INCOMEVW
AS
SELECT CLIENTID,COUNTRYNAME,SUM(OUTPUT.INCOME) AS INCOME
(SELECT EOCLIENT_ID AS CLIENTID,EOCOUNTRYNAME AS COUNTRYNAME,EOINCOME AS INCOME FROM EOCLIENT C INNER JOIN EOCOUNTRY CT ON
C.COUNTRYCODE=CT.COUNTRYCODE
[code]...
This is a sample view. As of now there is a full load happening from the source(select * from INCOMEVW) and loads to target table tbl_Income.We need to pick only the delta and load to the target table using a staging. The challenge is,
1) If we get the delta(Insert,update or deleted rows in the source tables EOCLIENT,EOCOUNTRY,ENCLIENT,ENCOUNTRY, how to load the incremental to
single target table tbl_Income.
2) How to do the Sum operation with group by in incremental load?
3) We are planning to have a daily incremental load and thinking to create the same table structure as source with Date and Flag column to identify
the date and whether that source row is an Insert or Update or Delete with the flag. But not sure how to frame something like this view and load to single target with Sum operations.
Hi,
I need to synchronize the data between two SQL Server 2005 Express.
The database is really simple, just few tables (but data are inserted very often) without any triggers.
Since Express edition could not behave as a Publisher I was thinking about implementing manually a sort of Replication mechanism... It should be a sort of Transactional Replication with queueud updating.
Does it sound a good idea?
Has anyone done this before o there are better solution?
Thanks in advance,
Stefano
Hi All,
This is the code for calculating a formula field in Crystal Reports.
I want to implement the same in Sql Server Reporting Services..
But it doesn't have the feature of For Loop....
The strings started with @ are formula fields....
Can anyone tell me, how can the below code be implemented in Sql Server Reporting Services
numbervar YR;
for YR := 1 to {@CalcFiscalAge} step 1 do
(
IF YR = {@CalcLifeCode} +1 then
locFactor1 := 1;
exit for;
IF (YR = 1 OR YR = {@CalcFiscalAge}) THEN
HALF_YEAR := 2
ELSE
HALF_YEAR := 1 ;
LINEAR := ROUND(REM/ ({@CalcLifeCode}-YR+1.5)/HALF_YEAR,4);
MACR := ROUND(REM / {@CalcLifeCode}*{@CalcSRate}/HALF_YEAR,4);
IF MACR >= LINEAR then
DEP := MACR
ELSE
DEP := LINEAR;
locFactor1 := locFactor1 + DEP ;
REM := 1 - locFactor1;
locFactor := locFactor1;
);
Thanks in advance
Regards,
Hi,
We are developing the web application using ASP.NET 2.0 using C# with Backend of SQL Server 2005.
Now we have one requirement with my client, Already our application live in ASP using MS Access Database. In Access Database our client already wrote the query, those query call the another query from same database. So I want to over take this functionality in SQL Server 2005.
In ASP Application, We call this query €œ081Stats€? from Record set.
This €˜081Stats€™ query call below sub query itself
Master Query: 081Stats
SELECT DISTINCTROW [08Applicants].school, [08Applicants].Applicants, [08Interviewed].Interviewed, [Interviewed]/[Applicants] AS [interviewed%], [08Accepted].Accepted, [Accepted]/[Applicants] AS [Accepted%], [08Dinged].Dinged, [Dinged]/[Applicants] AS [Dinged%], [08Waitlisted].Waitlisted, [Applicants]-[Accepted]-[Dinged] AS Alive, [08Matriculating].Matriculating, [Matriculating]/[Accepted] AS [Yield%]
FROM ((((08Applicants LEFT JOIN 08Interviewed ON [08Applicants].school = [08Interviewed].school) LEFT JOIN 08Accepted ON [08Applicants].school = [08Accepted].school) LEFT JOIN 08Dinged ON [08Applicants].school = [08Dinged].school) LEFT JOIN 08Waitlisted ON [08Applicants].school = [08Waitlisted].school) LEFT JOIN 08Matriculating ON [08Applicants].school = [08Matriculating].school;
Sub Query 1: 08Accepted
SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 2: 08Applicants
SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 3: 08Dinged
SELECT statusTbl.school, Count(1) AS Dinged
FROM statusTbl
WHERE (((statusTbl.decision)=0) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 4: 08Interviewed
SELECT statusTbl.school, Count(1) AS Interviewed
FROM statusTbl
WHERE (((statusTbl.interview)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 5: 08Matriculating
SELECT statusTbl.school, Count(1) AS Matriculating
FROM statusTbl
WHERE (((statusTbl.userdec)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
So now I got the solution from SQL Server 2005. I.e. Common Table Expressions, So I got the syntax and other functionality, Now my doubts is how do implement the CTE in SQL Server 2005, where can I store the CTE in SQL Server 2005.
How can I call that CTE from ASP.NET 2.0 using C#?
CTE is replacing the Stored Procedure and Views, because it is memory based object in SQL Server 2005.
How can I implement the CTE, where can I write the CTE and where can I store the CTE.
And how can I call the CTE from ASP.NET 2.0 using C#.
It€™s Very Urgent Requirements.
We need your timely help.
With Thanks & Regards,
Sundar
Hi,
I want to implement site search in my application, I got the solution of searching the static content, but most of the pages in my application is content based(content is coming from database at runtime), so those content I am not able to search from database.
While searching about the same, I got information about Full Text search (FT) in SQL Server 2005, but whether it will work in SQL Server 2005 Express or not?
I didnt get exact steps to implement FT on my database so far....
Any help?
Hi All,
I am now working on the design phase of my project, we are looking to implement Change Data Capture (CDC) but i need some help if you guys has implemented before using the SSIS 2005 componets. I am trying to use the Following:
Source---------Derived Column---------Lookup---------------Conditional Split (to split New records and Updated Records)-----------Destination. Respectively.
Lets make it clear, my source holds (Old records and newly added or Updated records), the Derived Column is to Derive new columns called Insert_Date and Update_Date. The Lookup i am Using is to look the Fact_Table(the Old Records) as Reference, and then based on this lookup i will split the records on timely based using the Conditional Split. My question is
1. Am i using the right components?
2. what consideration should i have to see to make it true (some Logics on the conditional split)?
3. Any script which helps in this strategy?
4. If you have a better idea please try to help me, i need you help badly.
Thank you,
SamiDC
I need to implement a file system for an application that allows me to roll back to a point in time. I can do this with either a journaling file system (Unix based such as JFS) or with a database file system such as Oracle Internet File system (now Oracle Content Services). I would MUCH prefer to use SQL Server but cannot find anything that supports this other than a 2000 ppt referencing the then up and coming SQL Server .NET File System.
The application(s) in question are older and store data in proprietary data files and need to access a local (or mapped) drive in standard form (d:programsmyprogram).
Does SQL Server 2005 or 2008 support this type of access? I have searched but cannot find anything to support this.
Thanks.
B.W.
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 RelatedI 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.
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
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.
I'm looking at a setup where they have server1 and server2 in a mirroring relationship with automatic failover.Â
Server1 is the principalThey are using transactional replication to replicate asingle databse to server3 is AWS.Distribution database is on Server1All Agents (log reader, snapshot, distributor) run on Server1Server2 has not been set up for replication...My understanding is that in this set up you would normallly place the distribution database on a separate server and enable publication on the mirror, Server2.
What happens if they failover? Replication would stop, and presumably records added while the mirror is the active database would not be marked for replication?How would they recover? Failback and reinintialize
Can 1 witness server work for 4 Principal and mirror servers?
Needs expert advise.
I have setup database mirroring for one big database.But now Transaction log is full and server is down. What should i do? We do bulk-insert in that database.
View 3 Replies View RelatedI 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
Hi,
1.) We have a sql server 2000 enterprise edition. Can we implement mirroing on that
2.) If the answer to the above is yes, Can I have mirror and witness as sql server 2005
Regards
skrcmr
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
I need to start mirroring the databases without witness server.
View 3 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.
I am unable to add witness server to my mirror configuration. my error log is throwing error'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89'.
View 5 Replies View RelatedHello,
Is SQL Server 2005 mirroring production ready yet. We have two servers and plan to set up mirroring between them. We have the Standard Editon installed on them.
Is Standard Edition sufficient or does it need Enterprise Edition?
Thanks in Advance,
Sarath R
Some how SQL Server 2005 database mirroring was partialy set up on one of my servers. I know this because there is traffic from the server to port 5022 for IP addresses 1.1.1.[234] (which means the last octect is either 2, 3, or 4).
I'm getting complaints from the network manager about the traffic and I want to turn this off, but I can't find any documentation on this topic. I've checked the database configuration and I can't find any configuration for mirroring, just the traffic on the TCP port.
Does this mean a background processes (service) is running that needs to be killed or is it more complex than that...
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
Hi
Can anyone please tell me what happens if I have Asynchronous mirroring setup and my Primary server physically dies and not available then what happens?. Does
1. Automatic failover occur to Secondary server?
2. What does the Database state show as. Primary, disconnected?.
3. what happens to my transactions. Are they lost?
4. Does any data loss occur?
If I rebuild a new server how do I sync back my current primary to the new one? In that case is it going to be just a fail back?
Any information is appreciated,
Thank you
AK
I develop a website. When I work in local machine for login/logout, The connectionstring works well.
<add name="DefaultConnection" connectionString="Data Source=(LocalDb)v11.0;AttachDbFilename=|DataDirectory|xxxxx.mdf;Initial Catalog=Restaurants;Integrated Security=True" providerName="System.Data.SqlClient" />
But When I deploy the web to IIS server. The I always can't work normally. The system cannot find the file specified..An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ComponentModel.Win32Exception: The system cannot find the file specified
Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
[Win32Exception (0x80004005): The system cannot find the file specified].[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 52 - Unable to locate a Local Database Runtime installation. Verify that SQL Server Express is properly installed and that the Local Database Runtime feature is enabled.)]