Sql-server – Copy Data between two databases

restoresql serversql-server-2012

I have the following situation. An application writes logs into SQL Server 2012 Standard edition database. The incoming data are too big to leave in the operational DB because the application also reads from this DB and it is time critical. So we need to delete the log entries which are older than a week.

But in some cases we need the old data. So my idea is to create a second database from a backup of the original and restore it every week before we delete the data. An already existing external reporting tool is ready to read data from two databases so our problem would be solved.

Is this possible – restoring and attaching data to another DB with the same schema – or do I need to create a new database each week?

Best Answer

There are 2 good options that you can think of implementing based on your comfort level :

Incremental Loads - using SSIS
- Pros:
  - You can schedule SSIS package as and when required.
  - You can do it for selected tables.
  - Depending on the frequency of data transfer, the data to be transferred would be less.
- Cons:
  - Would require a bit of learning if you are new to SSIS.
Backup Restore (sample script .. change it as per your needs) :
- Pros :
  - You get a copy of your database as per your schedule.
- Cons:
  - Entire database has to be restored. Requires more disk space.

Your application should use 3 part (dbname.schema.tableName).

Also, make sure that Instant file initialization is enabled to cutdown the restore time.

Related Solutions

Sql-server – Availability Group configuration & licencing

In order to redirect reporitng app or application with only read only queries you need to specify read only intent in application connection string (web.config or app.config in case client application is .NET)

The below is excerpts from SQL Server BOL

The application intent connection string property expresses the client application’s request to be directed either to a read-write or read-only version of an availability group database. To use read-only routing, a client must use an application intent of read-only in the connection string when connecting to the availability group listener. Without the read-only application intent, connections to the availability group listener are directed to the database on the primary replica."

Regarding your second question, if network throughput between two DC is OK you should be fine.

As long as any secondary replica is only solely used for DR (disaster recovery) and does not perform any work like read only request or backup you do not need to license it.

Sql-server – SQL Server data extract with limited performance impact

Many ways you can approach this scenario.

I need to do a data extract from the database for reporting purposes.

Backup restore approach is best suited if you want to do this on an infrequent basis and do a static reporting of data.
Database Snapshot is an enterprise version feature only and this also gives you a static point-in-time view of the database.

Note that database snapshots take up space on disk, and having too many can fill up your disk space, especially in a production environment where data is updated frequently. In addition, using database snapshots will degrade performance a bit because I/O is increased on the database due to the fact that the data pages are copied as the write operation is performed.

Refer to Microsoft SQL Server Database Snapshots and Synonyms to over come some limitations that snapshots have and workarounds.

Look into Replication especially T-Rep if you want to do near to real-time reporting and want to offload the reporting work from the primary server. T-Rep will give you the benefits of Transactional consistency, Low latency, High Throughput, Minimal overhead as well as the ability to filter rows.
Lastly if none of the options are feasible to you, then with proper testing look into changing the isolation level of the database to RCSI (Read Committed Snapshot Isolation). This will be at the COST of TEMPDB performance.
```
-- check if RCSI is enabled
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = '<dbname>'
-- Enable RCSI
ALTER DATABASE <dbname> SET READ_COMMITTED_SNAPSHOT ON
```

From The Data Loading Performance Guide :

RCSI basically will prevent queries reading data to block or be blocked by other queries modifying data in the same table. It is a powerful alternative to NOLOCK because it guarantees a complete, transactionally consistent view of the data and does not require a special hint. While RCSI originally targeted scenarios common to OLTP workloads, the feature can be a powerful tool in data warehouse workloads or scenarios involving large-scale bulk insert operations.

RCSI is enabled as a database-wide setting. When enabled, reader queries do not acquire shared locks on rows, pages or tables, and as a result they are not blocked by X or BU-locks taken by others. Instead, new or modified rows in a table carry a 17-byte version identifier, and the before-images of any rows being changed by a transaction (updated or deleted) are copied to tempdb using the row versioning mechanisms within SQL Server. Reader queries consider only those rows that were committed as of the start of the query – by ignoring any later version numbers and referencing tempdb for appropriate earlier versions of rows.

Also read up on : Comparing Different Results with RCSI & Read Committed from SQL CAT team.

LASTLY, to touch on NOLOCK hint, I would suggest you to read up

Best Answer

Related Solutions

Sql-server – Availability Group configuration & licencing

Sql-server – SQL Server data extract with limited performance impact

Related Question