Archive Database

Hi Everybody,
I'm working on a new database of 25GB in size with an expected 25% growth per year and an estimated 1000 TPS. Since I need to retain the old data for atleast 7 years. I would like to know whether I should Archive the database or is there any other means of storing the data, which will be used just to generate reports. Also pl. do let me know the advantages/disadvantages.

Thanks
KishoreYou said 7 years of total retention. But how old should the data be in your production environment? If 7 years as well, - you're looking at 200GB worth of data (I am yet to see an app that survives that long, but it's possible ;))|||archiving is dependent on your query requirements.
if your working set is getdate() - 6 months then archive
but if you report CONSISTENTLY all the way back. there obviously is no need to archive.
250GB is nothing to sneeze at but you can easily manage a db of that size with sql server 2k or any othe enterprise class db for that matter.

archiving really causes more headaches than it is worth.|||In production we need atleast 1 year old data and the rest can be archived which will be used to generate reports|||If 1 year is a working set then I'd recommed to have a reporting server where the archived data would be transferred to. You can also set this server as a linked server in your prod environment, and create additional views/stored procedures to have an easier access to the archived data without having to explicitly connect to a different server.

application role how query a view whose reference table is in another db?

Hi everybody.

I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1

I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)).

With the following query

SELECT USER_NAME()

I see that the approle is being used.

Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:

The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context.

What should I do to make it work?

The table in DB2 has the same schema of the view in DB1 which refers to it.

I put the DB1 TrustWorthy and both the database have the db_chaining option activated.

Any idea on how to solve the problem would be widely appreciated.

Thank you very much.

Vania

Application roles are not designed to work on cross-database scenarios. The following post has a detailed discussion on the topic: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1312863&SiteID=1

If your application relies heavily on cross-database access, I would recommend against using application roles. If you don’ have a choice, you can create a new execution context using EXECUTE AS in a SP. I wrote a small example that hopefully will be useful:

-- We will strat creating a low privielged login

--that will be used for the cross-DB context

-- and disable it to prevent any connection with it

--

CREATE LOGIN crossdb_login WITH PASSWORD = '50m3 p4zzwrd#'

ALTER LOGIN crossdb_login DISABLE

go

-- First we will go to our source DB to create the SP

-- and digitally sign it

--

use db_Source

go

CREATE USER crossdb_user FOR LOGIN crossdb_login

go

-- This SP will run under the 'crossdb_user' context

--

CREATE PROC sp_demo

WITH EXECUTE AS 'crossdb_user'

AS

-- display the token for testing purposes

SELECT * FROM db_Target.sys.user_token

-- select from a table

SELECT * FROM db_Target.dbo.data

go

-- Sign the modul and create a backup of the cert

-- in order to create a login for it

--

CREATE CERTIFICATE signing_cert

ENCRYPTION BY PASSWORD = '50m3 p4zzwrd#'

WITH SUBJECT = 'signing cert'

go

ADD SIGNATURE TO sp_demo

BY CERTIFICATE signing_cert

WITH PASSWORD = '50m3 p4zzwrd#'

go

BACKUP CERTIFICATE signing_cert TO FILE = 'signing_cert.cer'

go

-- Create a copy of the cert in master and a login mapped to it

--

USE master

go

CREATE CERTIFICATE signing_cert FROM FILE = 'signing_cert.cer'

go

CREATE LOGIN signing_cert FROM CERTIFICATE signing_cert

go

-- Now in the target DB we will create the user we need

-- and grant the appropriate permissions

--

use db_Target

go

CREATE TABLE data( value int )

go

CREATE USER crossdb_user FOR LOGIN crossdb_login

CREATE USER signing_cert

go

-- The certificate will be used to vouch for the

-- impersonated context

--

GRANT AUTHENTICATE TO signing_cert

go

-- GRANT SELECT on the table to the impersonated context

--

GRANT SELECT ON dbo.data to crossdb_user

go

-- Test the new signed SP

--

use db_Source

go

CREATE APPLICATION ROLE approle_demo WITH PASSWORD = '@.ppr0l3 d3m00'

go

GRANT EXECUTE ON sp_demo to approle_demo

go

EXEC sp_setapprole 'approle_demo', '@.ppr0l3 d3m00'

go

-- Should fail

-- Msg 916, Level 14, State 1, Line 1

-- The server principal "<<login_name>>" is not able to access the database "db_Target" under the current security context.

--

SELECT * FROM db_Target.dbo.data

go

-- Should succeed

EXEC sp_demo

go

Please let us know if you have any questions.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you so much and forgive me for my late but was on holidays.

Your solution works fine, in fact (I am sure you have no doubts about it).

A more question, please forgive me if it is obvious:

how, from inside the sp_demo, can I get the name of the server principal (domain\user if Windows authentication) which set the application role ? I mean the caller of the setapprole.

Thank you very much.

Vania

application role how query a view whose reference table is in another db?

Hi everybody.

I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1

I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)).

With the following query

SELECT USER_NAME()

I see that the approle is being used.

Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:

The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context.

What should I do to make it work?

The table in DB2 has the same schema of the view in DB1 which refers to it.

I put the DB1 TrustWorthy and both the database have the db_chaining option activated.

Any idea on how to solve the problem would be widely appreciated.

Thank you very much.

Vania

Application roles are not designed to work on cross-database scenarios. The following post has a detailed discussion on the topic: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1312863&SiteID=1

If your application relies heavily on cross-database access, I would recommend against using application roles. If you don’ have a choice, you can create a new execution context using EXECUTE AS in a SP. I wrote a small example that hopefully will be useful:

-- We will strat creating a low privielged login

--that will be used for the cross-DB context

-- and disable it to prevent any connection with it

--

CREATE LOGIN crossdb_login WITH PASSWORD = '50m3 p4zzwrd#'

ALTER LOGIN crossdb_login DISABLE

go

-- First we will go to our source DB to create the SP

-- and digitally sign it

--

use db_Source

go

CREATE USER crossdb_user FOR LOGIN crossdb_login

go

-- This SP will run under the 'crossdb_user' context

--

CREATE PROC sp_demo

WITH EXECUTE AS 'crossdb_user'

AS

-- display the token for testing purposes

SELECT * FROM db_Target.sys.user_token

-- select from a table

SELECT * FROM db_Target.dbo.data

go

-- Sign the modul and create a backup of the cert

-- in order to create a login for it

--

CREATE CERTIFICATE signing_cert

ENCRYPTION BY PASSWORD = '50m3 p4zzwrd#'

WITH SUBJECT = 'signing cert'

go

ADD SIGNATURE TO sp_demo

BY CERTIFICATE signing_cert

WITH PASSWORD = '50m3 p4zzwrd#'

go

BACKUP CERTIFICATE signing_cert TO FILE = 'signing_cert.cer'

go

-- Create a copy of the cert in master and a login mapped to it

--

USE master

go

CREATE CERTIFICATE signing_cert FROM FILE = 'signing_cert.cer'

go

CREATE LOGIN signing_cert FROM CERTIFICATE signing_cert

go

-- Now in the target DB we will create the user we need

-- and grant the appropriate permissions

--

use db_Target

go

CREATE TABLE data( value int )

go

CREATE USER crossdb_user FOR LOGIN crossdb_login

CREATE USER signing_cert

go

-- The certificate will be used to vouch for the

-- impersonated context

--

GRANT AUTHENTICATE TO signing_cert

go

-- GRANT SELECT on the table to the impersonated context

--

GRANT SELECT ON dbo.data to crossdb_user

go

-- Test the new signed SP

--

use db_Source

go

CREATE APPLICATION ROLE approle_demo WITH PASSWORD = '@.ppr0l3 d3m00'

go

GRANT EXECUTE ON sp_demo to approle_demo

go

EXEC sp_setapprole 'approle_demo', '@.ppr0l3 d3m00'

go

-- Should fail

-- Msg 916, Level 14, State 1, Line 1

-- The server principal "<<login_name>>" is not able to access the database "db_Target" under the current security context.

--

SELECT * FROM db_Target.dbo.data

go

-- Should succeed

EXEC sp_demo

go

Please let us know if you have any questions.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you so much and forgive me for my late but was on holidays.

Your solution works fine, in fact (I am sure you have no doubts about it).

A more question, please forgive me if it is obvious:

how, from inside the sp_demo, can I get the name of the server principal (domain\user if Windows authentication) which set the application role ? I mean the caller of the setapprole.

Thank you very much.

Vania

application login , but user security

Hi everybody...please don't flame me for my ignorance.
We are in the process of migrating from mainframe application using
vsam files to MICROFOCUS and SQL server
Here is the senario we are at now :
In mainframe, the application might be able to read and write to the
file, but the write access is controled by the RACF (security software
like active directory). In other words, even though USER A can execute
the application, he cannot write to it, because the security software
doesn't allow USER A to write to the file.
In the new environment, our online application are replaced by a
product called MICROFOCUS -ES_MTO. ES-MTO connects to sqlserver via an
application userid (lets say APPL1). USERA logins in to the ES-MTO
using login id USERA, but then ES-MTO connects to sqlserver using
APPL1. APPL1 has read/write authority on the tables. USERA should be
able to execute the application, so he can read the table, but
shouldn't be able to write to it.
The application is however a read/write application.
I hope I was clear enough on my sceneraio.
What I am hoping to find out is, how can I still use sqlserver to check
permission using the real user login id , when the application uses the
application userid to connect ?
Am I making sense ?
Any help or input is greatly appreciated .
ThanksSQL Server security context is determined by the login used to connect to
SQL Server or an application role enabled after the connection is made. If
ES_MTO uses a single login, you won't be able to implement a SQL Server
security model that allows you to control access based on an individual
user's identity unless your application code can conditionally enable an
application role. I know nothing about ES_MTO so I can't comment on whether
or not that approach is feasible. You can read about application roles in
the SQL Server Books Online
Hope this helps.
Dan Guzman
SQL Server MVP
"sql rookie" <anytasks@.gmail.com> wrote in message
news:1114703232.008696.232480@.g14g2000cwa.googlegroups.com...
> Hi everybody...please don't flame me for my ignorance.
> We are in the process of migrating from mainframe application using
> vsam files to MICROFOCUS and SQL server
> Here is the senario we are at now :
> In mainframe, the application might be able to read and write to the
> file, but the write access is controled by the RACF (security software
> like active directory). In other words, even though USER A can execute
> the application, he cannot write to it, because the security software
> doesn't allow USER A to write to the file.
> In the new environment, our online application are replaced by a
> product called MICROFOCUS -ES_MTO. ES-MTO connects to sqlserver via an
> application userid (lets say APPL1). USERA logins in to the ES-MTO
> using login id USERA, but then ES-MTO connects to sqlserver using
> APPL1. APPL1 has read/write authority on the tables. USERA should be
> able to execute the application, so he can read the table, but
> shouldn't be able to write to it.
> The application is however a read/write application.
> I hope I was clear enough on my sceneraio.
> What I am hoping to find out is, how can I still use sqlserver to check
> permission using the real user login id , when the application uses the
> application userid to connect ?
> Am I making sense ?
> Any help or input is greatly appreciated .
> Thanks
>|||sql rookie wrote:
What I am hoping to find out is, how can I still use sqlserver to check
permission using the real user login id , when the application uses the
application userid to connect ?
Am I making sense ?
My response:
I am unsure what you mean by using 'sqlserver to check permission using the
real user login id'?
If you setup the security on the MicroFocus environment using SQLServer
security (uid and password). This userid can be granted R/W access to the
tables while the real user id will not be granted them. You can then contro
l
access to writing/updating the tables via the application. If you want/need
to have the user be able to read the information outside of the application
(Access linked tables, Excel Queries, etc) you can grant the 'real user id'
read access to the tables/views.
Does this cover your question'
Mike
--
Mike Mattix
CP Kelco, Inc
Okmulgee, OK
"sql rookie" wrote:

> Hi everybody...please don't flame me for my ignorance.
> We are in the process of migrating from mainframe application using
> vsam files to MICROFOCUS and SQL server
> Here is the senario we are at now :
> In mainframe, the application might be able to read and write to the
> file, but the write access is controled by the RACF (security software
> like active directory). In other words, even though USER A can execute
> the application, he cannot write to it, because the security software
> doesn't allow USER A to write to the file.
> In the new environment, our online application are replaced by a
> product called MICROFOCUS -ES_MTO. ES-MTO connects to sqlserver via an
> application userid (lets say APPL1). USERA logins in to the ES-MTO
> using login id USERA, but then ES-MTO connects to sqlserver using
> APPL1. APPL1 has read/write authority on the tables. USERA should be
> able to execute the application, so he can read the table, but
> shouldn't be able to write to it.
> The application is however a read/write application.
> I hope I was clear enough on my sceneraio.
> What I am hoping to find out is, how can I still use sqlserver to check
> permission using the real user login id , when the application uses the
> application userid to connect ?
> Am I making sense ?
> Any help or input is greatly appreciated .
> Thanks
>