Solved

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

Posted on 2011-09-15
4
233 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
  • 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

932 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now