Solved

What is equivalent of Oracle  Archiver/Read only tablespaces in SQL server

Posted on 2011-09-15
4
237 Views
Last Modified: 2012-06-27
Hi All,
What does SQL server offer for oracle Read only tablespaces & transportable tablespaces?

I am also getting lost reading more on SQL server trasnaction logs.
Most posts recommend keeping just one transaction log file for each database. Duplexing logs can have issues they say which is not true in Oracle & highly recommended.

If the database is under FULL recovery mode I need to backup transaction log after defined interval. I want to know if there is something like Oracle Archiver process which can do this without dba intervention in background?

Many Thanks
0
Comment
Question by:crazywolf2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
rbride earned 500 total points
ID: 36541930
A tablespace in oracle corresponds roughly to a database in SQL Server (and Sybase).

What you want sounds a bit like READ ONLY filegroups. A filegroup is a set of files where you place your tables. Some of these can be marked read only.
[http://msdn.microsoft.com/en-us/library/ms190257.aspx]

You can place your tables or individual partitions of a table on such a read only filegroup.
[http://msdn.microsoft.com/en-us/library/ms174979.aspx]

You can also have databases completely read only.

Regarding transportable tablespaces. You can attach / detach databases in SQL Server. This is no problem with one caveat: the database users don't always match up to the server logins across servers. Tables that are partitioned can also be switched in and out.

regarding transaction log: It is only possible to have one transaction log (it is a table, syslogs) for each MSSQL database. But it is possible to have this log spread out over several devices (e.g. RAID) or to have the device that the log is on mirrored.
0
 

Author Comment

by:crazywolf2010
ID: 36542200
Hi,
Many thanks for your response. I have added my comments

>>> regarding transaction log: It is only possible to have one transaction log (it is a table, syslogs) for each MSSQL database. But it is possible to have this log spread out over several devices (e.g. RAID) or to have the device that the log is on mirrored.
-- How is this done? Is it done by creating filegroup which will spread files against underlying spindles?

>> If the database is under FULL recovery mode I need to backup transaction log after defined interval. I want to know if there is something like Oracle Archiver process which can do this without dba intervention in background?
-- How transaction log backup is done at SQL server? Please consider this as a highly x'actional OLTP system creating too many logs entries (insert/update/delete) every minute.
0
 
LVL 4

Assisted Solution

by:rbride
rbride earned 500 total points
ID: 36542802
Re: spreading out log.
If you are using e.g. a RAID 5 hardware disk, then the log will be spread out for you by the hardware.
(See: http://msdn.microsoft.com/en-us/library/ms190764.aspx).

There is an SQL Agent service [http://msdn.microsoft.com/en-us/library/ms189237.aspx] which can run all sorts of jobs, including backups.

You schedule jobs with the SQL Agent to backup databases [http://technet.microsoft.com/en-us/library/ms186865.aspx] and the log to disk.

You can also create maintenance plans [http://msdn.microsoft.com/en-us/library/ms187658.aspx] with the maintenance plan wizard instead of scripting the backup jobs yourself.

Databases and transaction logs can be backed up while the database is on line.
0
 

Author Closing Comment

by:crazywolf2010
ID: 36543024
Pointers Provided
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question