SQL Server 2005 Database Snapshots With Mirroring

Mar 8, 2007

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


ADVERTISEMENT

Mirroring & Snapshots?

May 16, 2008

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."

So how do you get the snapshot to the mirror?

View 2 Replies View Related

Database Mirroring Support Also By SQL Server 2005 Without SP1

Sep 27, 2006

Hi All,

I get a general question about the feacture "Database Mirroring". Is it support by SQL Server Standard. Edition without SP1?? Of i still need to upgrade the server from SQL Server 2005 to SQL Server 2005 with SP1?

Another question: are there any problem if the one machine(server) has installed SQL server 2005 Standard edition and the other with "Developer Editon".

I hope some one can answer my question. Thanks anyway!

Regards,

Pat

View 5 Replies View Related

SQL SERVER 2005 Database Mirroring For Large Number Of Databases

May 30, 2006

I am trying to enable database mirroring for 100 database.
It goes error free till 59 databases (some times 60 databases) with the
status (principal, synchronized) on principal. on the 60th or 61st database
it gave the status (principal, disconnected). Also mirror starts acting
abnormal. connection to mirror starts to give connection timeout and it is
not enabling database mirroring on any more databases. I have SQL SERVER
2005 Enterprise with SP1 on the servers. witness is not included yet.

this are my test servers... i have more than 500 databases on my production
servers.

principal and mirror both are using port 5022 for ENDPOINT communication.

View 1 Replies View Related

SQL Server 2005 Evalution Copy Database Mirroring In NT 4 Domian

Jul 17, 2007

Dear All,



I have a download "SQL Server 2005 Evalution Copy". I installed it to 3 machine, 1 - XP Professional (Witness) and 2 - Windows Server 2000 (Principle and Mirror) . And I want to try Database mirroring.



However, after restore the Log into mirror server I try to execute the following statement in Principle Server:

ALTER DATABASE TestDB
SET PARTNER=N'TCP://[10.0.200.2]:5022'
GO

The above 10.0.200.2 is the mirror server IP address.

it show error message as:

Msg 1416, Level 16, State 31, Line 1
Database "SCMPGroup" is not configured for database mirroring.



In Miorror Server

I execute the statement :

ALTER DATABASE TestDB
SET PARTNER=N'TCP://[10.0.200.1]:5022'
The above 10.0.200.1 is the principle server IP address.

It shows sucess



ALTER DATABASE TestDB
SET WITNESS=N'TCP://[10.0.15.146]:5022'

it show error message as:

Msg 1416, Level 16, State 1, Line 1
Database "SCMPGroup" is not configured for database mirroring.



Please help me! I down't know if NT4 domain or evalution copy problem in there.

View 3 Replies View Related

SQL SERVER 2005 DATABASE MIRRORING For Large Number Of Databases

Jun 1, 2006

I am trying to enable database mirroring for 100 database.
It goes error free till 59 databases (some times 60 databases) with the
status (principal, synchronized) on principal. on the 60th or 61st database
it gave the status (principal, disconnected). Also mirror starts acting
abnormal. connection to mirror starts to give connection timeout and it is
not enabling database mirroring on any more databases. I have SQL SERVER
2005 Enterprise with SP1 on the servers. witness is not included yet.

these are my test servers... i have more than 500 databases on my production
servers.

principal and mirror both are using port 5022 for ENDPOINT communication.

All of the databases are critical and all must be included in the Database Mirroring.
so, after that I tried to implement database mirroring again......
System has 3 GB of RAM, SQL SERVER (Mirror) using 85 MB of RAM but still
giving this error while trying to enable database mirroring for 37th
Database.....

"There is insufficient system Memory to run this query"

WHY?

View 19 Replies View Related

Article: Database Mirroring Not Ready For Production When SQL Server 2005 Ships

Sep 16, 2005

Can someone at Microsoft comment on this article, specifically, how it relates to SQL Replication?  Will SQL Replication fall into the category of what this article describes, or only the mirroring feature?

View 1 Replies View Related

Database Mirroring | Can Witness Live On Mirroring Server?

May 3, 2008



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?

View 7 Replies View Related

2005 Database Mirroring && Witness

Mar 14, 2006

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 Related

Mirroring For Certain Tables From The Database In Sql 2005

Nov 7, 2006

Can 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

View 4 Replies View Related

SQL 2005 Database Mirroring - AD Required?

Oct 10, 2005

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 Related

Database Mirroring Between 2005 Editions

Feb 1, 2007

Can I use Database mirroring between 2005 Std and 2005 Ent editions?

View 2 Replies View Related

SQL Native Client 2005 Connect To Database Mirroring

Apr 7, 2008



Hi all,

i have a small problem to connect SQL Native ODBC Client to a mirrored Database.
The connection goes without any problem when I connect to a primary Database, but when i give this connect string to an
ODBC Client ""Server=Partner_A; Failover Partner=Partner_B; Database=XXX; Network=dbmssocn"", then i get an connect fail.
There are no firewalls and other things on the network.
I am also able to connect to a mirror database during failover, but I'm not able to set both servers on the ODBC Connection.

Kind Regards,
Gruna

View 15 Replies View Related

Database Snapshots Performance

Feb 12, 2007

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:

CREATE DATABASE DB_SNAP ON
( NAME = DB_SNAP_Data, FILENAME =
'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDB_SNAP_Data.ss' )
AS SNAPSHOT OF DB;


And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?

Tanks.

F.

View 4 Replies View Related

Standard Edition Database Snapshots?

Apr 16, 2007

Hi. I'm evaluating performance features between Enterprise Edition and Standard Edition. I've heard that Database Snapshots are only available in Enterprise Edition, but cannot find anything that proves/disproves this.



Does anyone currently use Database Snapshots in Standard Edition SQL Server 2005? (Not talking about the Replication Database Snapshots.)



Quick script to try on a Standard Edition SQL Server:



CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;




Thanks!

View 4 Replies View Related

DB Engine :: Snapshots Remains In Database After Last CheckDB

Sep 5, 2015

Our last CheckDB failed on the 02/09. We were having problems with our SAN then and that got sorted out. The databases that it failed still has the snapshot showing in SQL Management Studio, screenshot exemple below (server name blanked out)

I reran CheckDB on the 04/09 on all the databases and the same databases that failed on the 02/09 failed again. I noticed in the log file, it tried to use the same snapshot file created on  02/09 to run CheckBD

I ran CheckDB manually on the databases that failed "DBCC CheckDB (WSS_Content_ce) with all_errormsgs" and did not get any errors back. 

How do I remove the snapshot databases? We had a problem on 02/09 so the snapshot copy obvisouly has problems. I'd like to remove these snapshots and rerun a CheckDB on all the databases to ensure all is ok.

View 9 Replies View Related

SQL Server 2005 Mirroring

Jul 20, 2007

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

View 4 Replies View Related

SQL Server 2005 Mirroring

Aug 16, 2006

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

View 3 Replies View Related

SQL Server 2005 Mirroring Problem.

Mar 16, 2007

Hi, 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.

View 3 Replies View Related

Which Edition Of Sql Server 2005 Provides Mirroring

May 10, 2006



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

View 3 Replies View Related

Server Down With Database Mirroring

Mar 16, 2008

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 Related

Mirroring :: Database Mirroring Changes In Application

Oct 12, 2015

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 Related

SQL Server 2005 Clustering/Replication/Mirroring

Sep 28, 2007

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

View 6 Replies View Related

Strange Login Issue With SQL Server 2005 Mirroring

Mar 27, 2008

Hi,
we have setup synchronous mirroring with witness server.We ran into problem when we stoped the SQLService of Primary Server. Failover Occured and Witness Server made Mirror Server Primary.

The User assosciated with the Database become Orphaned and no one is able to connect to the Database .
We recove login failed error for all login for mirrored database.
We have to manually run the below comand and reset the password for each login.

1.To Detect Orphaned Users
sp_change_users_login @Action='Report';
2.To Resolve an Orphaned User
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';
GO

Please let me know why sql server mirroring show such strange behavior. If we manually failove every thing work fine.

Regards
Sufian

View 3 Replies View Related

Mirroring :: Migrate Mirror Database To New Server

Apr 14, 2015

I need to migrate my SQL server Mirror Database to a new server. my current setup is as below

1. server A principal (192.168.1.100)

2. server B Mirror     (192.168.1.200)

Now i have a new server (Server C) to replace server B as below

1. server A principal (192.168.1.100)

2. server C Mirror     (192.168.1.300)

My question is how to migrate mirror db to new server without any affect or downtime on principal server.

note: SQL SERVER 2008R2 EE(64BIT), Win2008R2 EE 64bit.

View 5 Replies View Related

Log Shipping Vs Database Mirroring With SQL Server Cluster

Oct 24, 2007


Dear,

Are possible build a solution with SQL Server 2005 Cluster and Database Mirroring? I have the following scenario:

1) Site A
SQL Server 2005 with 2 nodes and active/passive

2) Site B
SQL Server 2005 for Disater Recovery with Log Shipping.

Are possivel change Log Shipping by the Database Mirroring? If is possible, are better:


a) Synchronous(with or without witness)
b) Asynchronous

Thanks a lot! Sandro.

sgpcosta@hotmail.com

View 2 Replies View Related

Database Mirroring Vs SQL Server FailOver (Performance)

Mar 15, 2006

Hi there.

I´m running some tests in a database with Mirroring and without Mirroring. As expected there is a performance hit using Database mirroring.

The tests i´m running are just simple functions inserting and updating the database, and then counting the number of sucedeed inserts and updates in a time interval.

My question here is: What if i use Sql Server failover mechanism?

I know that failover time will increase and management is more complex, but what can i expect in terms of performance ?





View 8 Replies View Related

Could Not Start Mirroring On A Single Laptop With Two Instances Of SQL Server 2005

Jan 3, 2007

I tried to set up mirroring on my laptop.

I have got two instances of SQL Server 2005 SP2 on my laptop (the first one is the default instance).

Checked configuration running the following scripts


SELECT type_desc, port FROM sys.tcp_endpoints;

GO

SELECT state_desc FROM sys.database_mirroring_endpoints

go

SELECT role FROM sys.database_mirroring_endpoints;

GO

SELECT 'Metadata Check';

SELECT EP.name, SP.STATE,

CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))

AS GRANTOR,

SP.TYPE AS PERMISSION,

CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))

AS GRANTEE

FROM sys.server_permissions SP , sys.endpoints EP

WHERE SP.major_id = EP.endpoint_id

ORDER BY Permission,grantor, grantee;

GO

Everything is OK on both instances. Tried to start mirroring and I have got this error message:

TITLE: Database Properties
------------------------------

An error occurred while starting mirroring.

------------------------------
ADDITIONAL INFORMATION:

Alter failed for Database 'Northwind'. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The server network address "TCP://kookaburra.sydney.ssw.com.au: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)

I could connect to both instances:

C:Documents and SettingsSergeiTchernykh.SSW2000>sqlcmd -U sa -P <password> -S k
ookaburra,5022
1> exit
C:Documents and SettingsSergeiTchernykh.SSW2000>sqlcmd -U sa -P <password> -S k
ookaburrasydney2005,5023
1> exit

I could ping my laptop

C:Documents and SettingsSergeiTchernykh.SSW2000>ping -a kookaburra

Pinging kookaburra.sydney.ssw.com.au [10.0.0.1] with 32 bytes of data:

Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128

Ping statistics for 10.0.0.1:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

Thank you.






View 4 Replies View Related

Data Base Mirroring In SQL Server 2005 Express Edition

Jan 16, 2007

HI,

Does SQL server 2005 Express Edition or

Does SQL server 2005 Express Edition Sp1 supports Data base Mirroring?

View 3 Replies View Related

SQL Server 2008 :: Database Mirroring Timeout Setting?

Oct 20, 2011

I have setup database mirroring with witness server. To prevent unnecessary failover because of network slow or other issue, I changed the timeout setting as

ALTER DATABASE <Database Name>
SET PARTNER TIMEOUT 120

Which I understand if connection is broken between principal and mirror, principal database will wait for 120 second and after that only automatic failover will happen.

If this is true, it does not happen in my case. Failover happens before120 second.

View 5 Replies View Related

SQL Server Admin 2014 :: Mirroring Database Limits

Nov 22, 2014

I have multiple SQL 2008 severs with databases. Also, 1 mirroring server in place.

Since my database count is increasing can i have only 1 mirroring server. Is there any limit of db at mirroring server. I would have approx. 150 databases.

View 4 Replies View Related

SQL Server 2008 :: Encryption / Decryption On Mirroring Database

May 4, 2015

I have created mirroring... one of the column is encrypted on mirror database and I can see the decrypted result when I do query when I actually logged into server (through remote connection) but when I use the same query through using SSMS from my laptop the query result come as the column is not decrypted,

View 0 Replies View Related

Mirroring :: Manual Failover Did Not Get Back Database On Secondary Server?

Jun 22, 2015

I have SQL Server 2008 R2 database mirroring setup with witness. Below database connection string is used by the application
 
jdbc:sqlserver://<Server_A>MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>MSSQLSERVER
 
During some maintenance activity at the OS side , I tried to manually failover database from Server_A to Server_B. It took long time for failover  to Server_B. While failing over the Mirrored_DB database goes in In Recovery status and application is not able to connect to database. I waited for long time more than 40 mins for database to come online on Server_B but it did not work.So I have recovered database on Server_B using RESTORE DATABASE Mirrored_DB WITH RECOVERY.
 
After database was recovered on Server_B application was not able to connect to database with error as "The database <Mirrored_DB> on server Server_BMSSQLSERVER is not configured for database mirroring".
 
My Question is

Why manual failover did not get back database on secondary server upwhy the original  connection string below is not able to connect to the database while database is online on Server_B  but without mirroring

 jdbc:sqlserver://<Server_A>MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>MSSQLSERVER

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved