EzDevInfo.com

Don't quite understand SQL Server transaction logs

I've read up on SQL Server transaction logs, but still not entirely comfortable with how to use/manage them. I get that they are important for things like transaction rollbacks, mirroring, replication and log shipping etc.

To me, they still seem like a black box and I'm not entirely comfortable doing anything with them. Are there tools that allow me to view a transaction log file or get information about it? If I don't need things like transaction log shipping, is it ok for me to shrink and/or truncate my log files periodically? Particularly in the case of restoring backups onto a test instance - do I really need a multi-gigabyte log file taking up space? What other features do I need to be aware of that have particular dependency on transaction logs and would not work if I shrank/truncated log files?

Segfault on 2nd connection with pyodbc to mirrored MS SQL Server

I'm facing an issue with a Python script that connects to a mirrored MS SQL server DB. It's throwing a segmentation fault when I try connecting to the DB for the second time. Both the app server and the DB instances are running on Google Compute Engine.

Here's some code replicating the issue:

import pyodbc

params = {
   'autocommit': True,
   'uid': 'myuser',
   'tds_version': '8.0',
   'DRIVER': '{mssql}',
   'pwd': 'mypassword',
   'server': 'sql-server-01',
   'database': 'mydb',
   'port': 1433,
}

c1 = pyodbc.connect(**params)
c2 = pyodbc.connect(**params)

The first connection (c1) succeeds, but the second connection (c2) fails immediately with segfault. "mydb" is mirrored to a second server (sql-server-02). Using a non-mirrored DB, or disabling mirroring for this DB, makes it go away.

We have tried upgrading several libs, and that didn't fix the issue. Versions:

• Microsoft SQL Server: 12.00.2000 (latest)
• Python: 2.7.6
• pyodbc: 3.0.10 (latest)
• unixODBC: 2.2.14p2-5ubuntu5, 2.3.0, 2.3.4 (latest)
• MS ODBC driver for RedHat: 11.0.1790.0, 11.0.2270.0 (latest)

To add here, Java code performing the same steps works fine.

Any ideas?

Database mirroring a live database

I've been reading about database mirroring on SQL Server 2008 R2 / 2012 and I have a question: does it matter if the database being mirrored is live? Do I have to do anything special or different if my database is live?

EDIT-1:

• I have a (one) database on premises that's our production database. A few hundreds transactions happens per second, and 99.999% are read operations. Our plan is to mirror this database (single database) to SQL Azure (IaaS) as a "backup" structure, if the on premises environment crash for any reason, we just point to the cloud.
• I would like a zero loss, but I settle for a 5 seconds loss
• Latency wont be a problem... slow running is better than no running...
• I have just one node and one database

Whats the best redundancy setup on AWS for SQL Server 2014

We're migrating our environment over to AWS from a colo facility. As part of that we are upgrading our 2 SQL Server 2005s to 2014s. The two are currently mirrored and we'd like to keep it that way or find other ways to make the servers redundant. # of transactions/server-use is light for our app - but it's in production, requires high availability, and, as a result, requires some kind of fail over.

We have already setup one EC2 instance and put SQL server 2014 on it (as opposed to using RDBMS for licensing reasons and are now exploring what to do next to achieve this.

What suggestions do people have to achieve the redundancy we need?

I've seen two options thus far from here and googling around. I list them below - we're very open to other options!

First, use RDBMS mirroring service, but I can't tell if that only applies if the principal server is also RDBMS - it also doesn't help with licensing.

Second, use multiple availability zones. What are the pros/cons of this versus using different regions altogether (e.g., bandwidth issues) etc? And does multi-AZ actually give redundancy (if AWS goes down in Oregon, for example, then doesn't everything go down)?

Thanks for the help!

Log Shipping vs Replciation Vs Mirroring in SQL server 2012

I have a SQL Server 2012 database which currently used as a transactional database and reporting database. The application reads/writes into the same database and the reports are also generated against the same database.

Due to some performance issue, I have decided to maintain the two copies of the database. One will be a transactional database which will be accessed by the application. The other database will be the exact copy of the transactional database and it will only be used by the reporting service.

Following are the requirements:

1. The reporting database should be synched with transactional database in every one hour. That is, the reporting database can have stale data for maximum of 1 hour.

2. It must be read-only database.

3. The main intension is NOT recovery or availability.

I am not sure which strategy, transactional log shipping, mirroring or replication, will be best suited in my case. Also if I do the synch operation more frequently (say in every 10 minutes), will there be any impact on the transactional database or the reporting service?

Thanks

How to keep log-shipping up after failover (mirroring) in sql server

In SQL Server 2008,I have three instances which are LOG-SHIP, PROD, and MIRROR and I keep my transaction logs on different disks.

For instance my PROD transaction file is located under C:\MSSQL10.PROD\MSSQL\TRAN_PROD. , MIRROR transaction file is located under C:\MSSQL10.PROD\MSSQL\TRAN_MIRROR and lastly LOG-SHIP transaction file is located under C:\MSSQL10.PROD\MSSQL\TRAN_LOGSHIP

As you can guess there is mirroring between PROD and MIRROR. In addition, There is log-shipping between PROD and LOG-SHIP.

What I want to learn is this :

Once I failover between databases which are PROD and MIRROR, PROD act as MIRROR and MIRROR act as PROD as we can expect.

In MIRROR database (which is PROD now after failover), it can backup log files by means of bakup_log job. But the job of LOG-SHIP instance can not copy transaction logs due to changed network path. Thus i need to set-up log-shipping between LOG-SHIP and the instance which is currently used as PROD. Instead of setting up log shipping what i can do in order to automate the system. In other word, i just want to make small changes after this failover issue and make the system up again.

Realtime one-way mirroring of a SQLite database

I am dealing with a 3rd party application that's running a SQLite 3 database with WAL (Write-Ahead Logging) on a local computer, and I'm looking to mirror that database (read only, this is a one-way mirroring) to another system. The challenge is that I'm running in a separate process, which seems to complicate things somewhat.

The database is being created and opened with a normal locking mode so there's no problem reading it from another process, but I'm trying to either find an existing implementation or get some pointers on where to get started. My understanding, based on other posts is that the standard sqlite update hooks (such as sqlite3_update_hook) will not work out of process.

A key issue is speed, I'd like to ideally be able to detect each update as soon as it happens and begin transmitting it. This means that most polling options would be out of the question, but even if they were, how would you detect the most recent changes?

I'm seeing two files that look promising: the actual WAL file (foo.db-wal), and that memory mapped index file (foo.db-shm). I'm hoping that those two contain the information I need to: A. Detect when changes occur in the database and B. Be able to grab just the incremental changes since the last update.

But a pointer to some existing solution would be much preferred... :-)

Database mirroring between different versions of sql server instances

We had two SQL Server 2008R2 instances A and B. We used database mirroring for a 1TB database from A to B - created a database snapshot on B for that 1TB database (which took 5 seconds) - and used that database snapshot for reporting.

We upgraded instance B to SQL Server 2014.

Is there way to mirror a SQL Server 2008R2 database into SQL Server 2014 instance?

Microsoft prerequisite mentions that

"For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server".

Currently we have been doing backup-restore but it takes a 2 hours. We would like bring down the time to take the snapshot of the database from instance A to B.

Are there any other ways?

Upgrading sql server database that is being mirrored - how to?

OK so I've been tasked with upgrading an application which involves updating schema in a sql server 2005. Now I've been told their dr environment has a mirrored database. I've read the msdn article on mirroring but am unclear how it impacts my upgrade.

Do I: a) want to stop the mirroring (someone mentioned a wizard, but I'm not sure how I go about doing this) b) keep the mirroring in place because whatever I run on the primary database will flow through to the mirror

If I stop the mirroring do I then upgrade each database according to my scripts and then somehow set up the mirroring again? Or do I just apply my scripts to the primary database and do some kind of restore and set that up as a mirror.

As it is an existing mirror in a production environment I don't want to poke around too much in case I do something bad. Is there an easy way to grab their current settings so I can easily re-establish the mirror if I do have to stop it?

Thanks!

Edit - my other question is, with the transaction logs it looks like its being backed up every 15 minutes according to a schedule. Should I change the backup schedule whilst doing the deployment? I think one of my redgate schema changes scripts will take over 2 hours to run :) It is a big database!

Reporting Services and SQL Server Mirroring

In an installation with 2 SQL Server 2008 SP2 Standard servers we use mirror to have failover partner for the databases.

One of the purposes of the databases are to supply reports through SQL Server Reporting Services.

My question is how I set this up to have failover of the reports AND keep cost at a minimum.

I can see one solution where the reporting databases are mirrored as well as the user databases they use. But will the reporting services failover?

Another way could be to install 2 separate instances of reporting services on each database server where the reporting databases are not mirrored and both are running. But then how do I redirect the users browsers from one entry address to a running reporting service?

There's also a witness server running SQL Server 2008 Express. This could be upgraded to standard and run the reporting services (should be able to failover through native client connection), but then the reporting service it self has no failover.

I don't know if I'm missing something here but is it impossible to do this in a good way without using SQL Server Enterprise and a failover cluster solution?

Any ideas, thoughts and links would be appreciated, but be aware that I have read a lot of guides and best practices around the internet and haven't found any that answers this question so please only post relevant links.

Any info I gather from you post, links and my research and testing will of course go back to Stackoverflow for future reference for others as I can't imagine I'm the only one who would like such a solution.

• Smint

SQL Server failover for mirror database in SQL Sever 2014

I am using sql server 2014 with mirroring. My .net application goes into non responding mode when trying to connect to database when I use the connection string below.

Server=Server1IP; Failover Partner=Server2IP; database=dbname; uid=sewebsite;pwd=somerndpwd;Connection Timeout=0;Pooling=True

But it works in below cases:

• If I remove mirroring and I use above connection string.
• If I remove "Connection Timeout" parameter from connection string and use mirroring.

Please suggest the possible reasons behind this issue.

Database Mirroring doesn't work (SQL Server 2008)

I have a problem with the database connection. There are two database server: DBS1, DBS2

DBS1 (primary server) DBS2 (mirrored server)

I connect with the following Connection String:

Data Source=DBS1;Failover Partner=DBS2;Persist Security Info=True;Initial Catalog=database;User ID=xxx;Password=xxx

When I run my program on my PC it works, but when i try the program on the webserver in the DMZ the failover doesn't work.

Can you tell me some configurations which can be the reason for this?

SQL Server 2008 R2 log files filled up the drive

So some of us dev's are starting to take over the management of some of our SQL Server boxes as we upgrade to SQL Server 2008 R2. In the past, we've manually reduced the log file sizes by using

    USE [databaseName] 
    GO 
    DBCC SHRINKFILE('databaseName_log', 1) 
    BACKUP LOG databaseName WITH TRUNCATE_ONLY 
    DBCC SHRINKFILE('databaseName_log', 1)

and I'm sure you all know how the truncate only has been deprecated.

So the solutions that I've found so far are setting the recovery = simple, then shrink, then set it back... however, this one got away from us before we could get there.

Now we've got a full disk, and the mirroring that is going on is stuck in a half-completed, constantly erroring state where we can't alter any databases. We can't even open half of them in object explorer.

So from reading about it, the way around this happening in the future is to have a maintenance plan set up. (whoops. :/ ) but while we can create one, we can't start it with no disk space and SQL Server stuck in its erroring state (event viewer is showing it recording errors about 5 per second... this has been going on since last night.)

Anyone have any experience with this?

SQL server semi-database mirroring

We have the following problem:Our customer has a the life database.Sometimes,we face bugs that are due to data in life,those bugs doesn't appear in our staging and development databases because they are usually related to the actual data. So, for accurate debugging,we need to have the same copy of life data in another database.This database should be synchronized with the life database (either automatically or in-demand),so that we can replicate the erroneous scenarios without impacting the actual data.How can we perform that?Is it better to create this "semi-mirror" in the staging itself? As a final note,I don't want the changes from the "semi-mirror" database to be reflected one the life, Only from the life to the "semi-mirror".

How to disable Filestream on SQL Server 2008 R2 to enable mirroring?

I'm enabling database mirroring on SQL Server 2008 R2, I've already configured my servers but when trying to start mirroring I get the following error:

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

An error occurred while starting mirroring.

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

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

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&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)

------------------------------

A database cannot be enabled for both FILESTREAM storage and Database Mirroring. (Microsoft SQL Server, Error: 5574)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000&EvtSrc=MSSQLServer&EvtID=5574&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

I don't even have files on my database so I'd like to turn off filestream, how can I do it?

Thanks