Solved

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

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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