January 28, 2013 at 4:17 pm
Can you you arrange to copy the file to a location the test server can access? You could add this to the backup job or use a separate process.
January 28, 2013 at 6:12 pm
I got mirroring to work in the testing environment. Now how can I write queries against it?
January 28, 2013 at 10:15 pm
Here is a link related to Creating a database snapshot.
http://msdn.microsoft.com/en-us/library/ms175876(v=sql.105).aspx
You can go ahead a create a snapshot based on the Mirrored Database that you have (in Synchronized state). There are other things to consider in a snapshot, like how sparse files are used, the space usage as presented on the explorer vs. the actual disk usage.
basic syntax:
CREATE DATABASE <DatabaseSnapshotName>
(NAME = '<Logical Data File Name>', FILENAME = '<Valid Snapshot Full path and filename>')
AS SNAPSHOT OF <Databasename>;
For a Database called DB1:
CREATE DATABASE DB1_SS1
(NAME = 'DB1', FILENAME = 'E:\MSSQL\Snapshot\DB1_Snapshot.SS1')
AS SNAPSHOT OF DB1;
And then you can go ahead and issue :
USE DB1_SS1
GO
SELECT ..... your queries...
January 28, 2013 at 11:43 pm
Snapshot is read-only. If you want to test something other than reads, it will not work.
Snapshot might not be good to test even the reads because it is physically different than original database. The query you are trying to test will partially read the data files of snapshot database and partially mirror database.
Restored test db is read-write, and is exact copy of production. It doesn't change production server, as mirror does. Mirror prevents certain operations on your production system. For example, you cannot do maintenance anymore in bulk_logged recovery model on your production. Synchronized mirror will slow down your production server - it will wait until the transaction log records are written to the transaction log of the mirror. And what will happen if, accidentally, you fail-over to the mirror db which is on a test system, and your production becomes not read-only but unreadable?
Mirror is a great HA feature, don't get me wrong, but it is a completely wrong tool for the purpose you need. I can't believe that I'm the only one here telling you this.
January 29, 2013 at 3:04 am
Vedran Kesegic (1/28/2013)
Snapshot is read-only. If you want to test something other than reads, it will not work.Snapshot might not be good to test even the reads because it is physically different than original database. The query you are trying to test will partially read the data files of snapshot database and partially mirror database.
Restored test db is read-write, and is exact copy of production. It doesn't change production server, as mirror does. Mirror prevents certain operations on your production system. For example, you cannot do maintenance anymore in bulk_logged recovery model on your production. Synchronized mirror will slow down your production server - it will wait until the transaction log records are written to the transaction log of the mirror. And what will happen if, accidentally, you fail-over to the mirror db which is on a test system, and your production becomes not read-only but unreadable?
Mirror is a great HA feature, don't get me wrong, but it is a completely wrong tool for the purpose you need. I can't believe that I'm the only one here telling you this.
I would have said all that if I'd been back sooner 🙂
I agree with Vedran that mirroring really isn't what you need in this situation.
Cheers
Gaz
January 29, 2013 at 10:56 am
Okay, what's the best way to disable mirroring? 🙂
Let me explain the situation. The system being tested is based on a data warehouse. First, we have SSIS packages that extract data from text files and other sources into the staging database. Those packages run once a day, in the morning. Then we have another set of SSIS packages that do the necessary transformations and load data from staging into the data warehouse. The staging databases in the old version and the new version are essentially the same structurally. However, the data warehouses are very different. I would like the staging database in the test environment to have the same data has the staging database in the production environment.
Now that we've ruled out mirroring, what should I do? Would a snapshot of the production staging database work? Should I go the backup/restore route? The problems with that approach are: 1) the production backups are located in a place the test systems can't reach, 2) the production backup occurs late in the day, so the test based on the restore will actually be a day behind.
January 29, 2013 at 11:02 pm
imani_technology (1/29/2013)
1) the production backups are located in a place the test systems can't reach
the easy or safer way is , copy the backup to drive which is accessible to test environment then do the restore.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 30, 2013 at 10:14 am
Vedran Kesegic (1/28/2013)
Snapshot is read-only. If you want to test something other than reads, it will not work.Snapshot might not be good to test even the reads because it is physically different than original database. The query you are trying to test will partially read the data files of snapshot database and partially mirror database.
Restored test db is read-write, and is exact copy of production. It doesn't change production server, as mirror does. Mirror prevents certain operations on your production system. For example, you cannot do maintenance anymore in bulk_logged recovery model on your production. Synchronized mirror will slow down your production server - it will wait until the transaction log records are written to the transaction log of the mirror. And what will happen if, accidentally, you fail-over to the mirror db which is on a test system, and your production becomes not read-only but unreadable?
Mirror is a great HA feature, don't get me wrong, but it is a completely wrong tool for the purpose you need. I can't believe that I'm the only one here telling you this.
Actually, he only needs to do reporting off the data, so mirroring IS what he needs and he already has it working. He isnt testing inputs, only outputs. As long as he understands the temporary issue with performance during the test period and can live with that, mirroring ISNT that big of a boogeyman.
January 31, 2013 at 3:10 am
DiverKas (1/30/2013)
Vedran Kesegic (1/28/2013)
Snapshot is read-only. If you want to test something other than reads, it will not work.Snapshot might not be good to test even the reads because it is physically different than original database. The query you are trying to test will partially read the data files of snapshot database and partially mirror database.
Restored test db is read-write, and is exact copy of production. It doesn't change production server, as mirror does. Mirror prevents certain operations on your production system. For example, you cannot do maintenance anymore in bulk_logged recovery model on your production. Synchronized mirror will slow down your production server - it will wait until the transaction log records are written to the transaction log of the mirror. And what will happen if, accidentally, you fail-over to the mirror db which is on a test system, and your production becomes not read-only but unreadable?
Mirror is a great HA feature, don't get me wrong, but it is a completely wrong tool for the purpose you need. I can't believe that I'm the only one here telling you this.
Actually, he only needs to do reporting off the data, so mirroring IS what he needs and he already has it working. He isnt testing inputs, only outputs. As long as he understands the temporary issue with performance during the test period and can live with that, mirroring ISNT that big of a boogeyman.
Fair point - guess I read UAT as app testing too - although looking at the first post again it could just be reporting.
Even so, I'd still go with backup/restore - more flexible, less scope for things going wrong, less admin, less/no overhead on production, disk space?, plus wouldn't datasources need to be changed in the reports as the db snapshot name would be different to the real db name?
Gaz
January 31, 2013 at 5:41 am
Reporting on the mirror server would be fine if that mirror is a production server.
We have test server here which is not monitored, does not have SLA, failover is not tested or prepared,
principal send queue is not monitored.
In other words, pairing production principal with unmonitored test server mirror is a bad idea.
Pairing production principal with production mirror that serves as reporting server and is monitored would be just fine.
But we don't have that here.
We have a test server which is for testing purposes. And radically changing production setup just to do something on the test and pair it tightlz with the test - you can do that, but prepare a good CV 🙂
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply