July 8, 2008 at 1:17 am
hi all,
in my current organisation the production dbs and the devlopment dbs resides on the same m/c. my task is to seperate this two environments and restrict unncessary access to the developers.
my checklist is as follows
1. create two diff servers - one for test dbs, one for prod dbs
2. transfer/remove all the logins to the respective servers using sp_helprevlogin
3. transfer all the object permissions
4. move all the jobs if any
5. create proper maintanance plans and housekeeping jobs for the production dbs
is there anything else that i am missing?
also i need to implement auditing
check for unathourised users accessing the d/bs and generate a report. will mom be sufficient to handle this?
how do i generate a report. is there anything else that i need to consider in terms of security
thanks for your help.
sharon.
July 8, 2008 at 10:35 am
Looks like a pretty good list.
A note on step 3: if you backup and restore the databases to the new instance, the users and object permissions will be moved with the databases. You may need to sync some users with logins using sp_change_users_login.
If you have DTS packages, you'll need to move them also. Are the test and production environments going to be on separate physical servers or will they be two instances on the same server?
As far as auditing, we don't use MOM so I don't know if it will do what you want. We do have a server where we run server-side trace to record who does what.
Greg
July 8, 2008 at 10:53 pm
thanks greg for your response.
we are planning to move the production dbs to vmware . where as the test db will be on the physical m/c which is now being used for both test/prod dbs.
how do i generate a server side trace and capture the details?
July 9, 2008 at 6:08 am
As far as moving jobs, if you backup/restore your original msdb, you won't have to recreate any jobs, simply delete the ones that are no longer necessary. You will have to update msdb.sysjobs originating_server to the new instance/server name but that's it. As far as vmware, be aware of your resources and how they are allocated. We're still working out a few kinks here (number of processors allocated, memory..). It's not horrible but something different (as opposed to a single dedicated machine per instance) that I am trying to deal with. Long term, it should work. Short term, a few growing pains.:crazy:
-- You can't be late until you show up.
July 9, 2008 at 6:17 am
hi terry,
thanks for your response.
please could you guide me as to what things i need to take into condiderations while moving the d/b from physical servers to vmware.
July 9, 2008 at 6:31 am
with SQL2000 sp_helprevlogin does not take across default database or language, so script those out from the source server and run on the destination once dbs created.
set quoted_identifier off
select 'sp_defaultdb ',+"'" +loginname +"'," +dbname from syslogins
select 'exec sp_defaultlanguage ',+"'" +loginname +"'," +language from syslogins.
why put the prod server on vmware and test on dedicated machine? Surely less risky and impact on current prod to do it the other way round?
---------------------------------------------------------------------
July 9, 2008 at 6:34 am
Really not much to it. What we've done is shutdown the original SQL server services and COPIED the mdf and ldf files to the new location then simply used sp_attachdb to attach the databases to the new server/hard drives. Very straight-forward. Or you can use backup/restores to do the same thing. In our case it would've taken substantially more time to do it this way.
With respect to vmware, we initially had to toss more memory at SQL server. We were in a consolidation mode from 3 physical servers (total of 8 GB of memory allocated to all three) so we went with the combined specs of the three and sized up vmware to meet those specs. Ultimately we added another 8 GB of RAM to finally see performance boosts. My personal opinion is it's the RAID5 configuration, per the vendor recommendation. I've seen and read about ALL kinds of performance issues with RAID5 and OLTP system. I can't seem to get the infrastructure guys to see things my way but I'm still trying to make my case and force this issue.
Also, and I am no vmware expert, we had to allocate a couple of additional processors to the SQL environment. Seems infrastructure was trying to give out as few resources as possible to slice/dice/add additional applications to the server. Makes a great point for them with management to say "look, we consolidated 3 physical servers onto one. Think of the money and ongoing maintenance costs we saved". What they fail to mention is that our users are frustrated with poor performing applications. And who do those issues come back to? You guessed it, me. Try and do as much homework on vmware (google it or search this site) as you can and make a valid case for what you percieve to be the appropriate way to move forward for your environment.
Edit - George said: "why put the prod server on vmware and test on dedicated machine? Surely less risky and impact on current prod to do it the other way round?". I couldn't agree more!
-- You can't be late until you show up.
July 9, 2008 at 6:58 am
thanks george for your help.
i did not understand what you meant by this statement.
why put the prod server on vmware and test on dedicated machine? Surely less risky and impact on current prod to do it the other way round?
after moving the prod dbs from the physical server to vmware since these m/c's will be availabe we thought of moving the test and the qa environment on the dedicated machines instead of wasting vmware resources.
isn't moving productions db's to vmware better?
is there another way to do this entire thing which will be faster and less risky?
July 9, 2008 at 7:12 am
isn't moving productions db's to vmware better?
I wouldn't move Production to VMWare if I had a choice... Development? Sure... just not Prod.
--Jeff Moden
July 9, 2008 at 7:21 am
Production on VM is definitely not recommended. In addition the comment about just restoring MSDB for the DTS move being sufficient, is entirely dependent on where your DTS/SSIS packages are stored. If they are on the file system then obviously this is not going to work.
In regards to Auditing - you would need to provide more information in order to determine if MOM was sufficient. Are you talking about Configuration Management level auditing, SOX level auditing, perhaps FDA level compliance auditing - for the later two MOM wil not be sufficient.
July 9, 2008 at 7:52 am
Sharon,
I got the impression you were moving off the prod databases and leaving the dev databases on the existing machine. I dont know how powerful your existing machine is but if you leave the prod databases on the existing machine you are not exposing the prod environment to the risks of the move.
As you will see in many posts on this forum and others using vmware for SQL which will support any load is liable to lead to performance problems. We have just done a large migration and some sql servers have been placed on vmware to save money (no, they didn't ask me first) and for some its handling it ok, for the one box with a heavier load all of a sudden we are getting timeouts from the app, and whos gotta try and fix it.............
At first this same box ramped up to 100% cpu when running dbcc or any queries from large tables (few seconds run time went up to many minutes) and it was all to do with how trhe resources had been allocated across the virtual machines. So be warned.....
---------------------------------------------------------------------
July 9, 2008 at 9:07 am
how do i generate a server side trace and capture the details?
Here's what we run in a DTS package. You can put it in a stored procedure, a T-SQL job step, or a batch file. The results are stored in a file with the current data appended to the name. Since this is on a DW server, we stop it before the nightly ETL and start it after.
You can see the parameter values by looking up "sp_trace_setevent" and "sp_trace_setfilter" in BOL.
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
DECLARE @TODAY as varchar(10)
declare @TraceFile nvarchar(245)
set @maxfilesize = 5
--FORMAT today's date to 'yymmdd'
SET @TODAY = (
RIGHT(CAST(DATEPART(YEAR,getdate())AS CHAR(4)),2)
+
CASE LEN(CAST(DATEPART(MONTH,getdate())AS CHAR(2)))
WHEN 1
THEN '0'+CAST(DATEPART(MONTH,getdate())AS CHAR(1))
ELSE
CAST(DATEPART(MONTH,getdate())AS CHAR(2))
END
+
CASE LEN(CAST(DATEPART(DAY,getdate())AS CHAR(2)))
WHEN 1
THEN '0'+ CAST(DATEPART(DAY,getdate())AS CHAR(1))
ELSE
CAST(DATEPART(DAY,getdate())AS CHAR(2))
END)
set @TraceFile = N'G:\Trace_Log\Prod_Trace_' + @TODAY
exec @rc = sp_trace_create @TraceID output, 2, @TraceFile, @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 11, 1, @on
exec sp_trace_setevent @TraceID, 11, 3, @on
exec sp_trace_setevent @TraceID, 11, 8, @on
exec sp_trace_setevent @TraceID, 11, 10, @on
exec sp_trace_setevent @TraceID, 11, 11, @on
exec sp_trace_setevent @TraceID, 11, 12, @on
exec sp_trace_setevent @TraceID, 11, 14, @on
exec sp_trace_setevent @TraceID, 11, 23, @on
exec sp_trace_setevent @TraceID, 11, 26, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 23, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 42, 1, @on
exec sp_trace_setevent @TraceID, 42, 3, @on
exec sp_trace_setevent @TraceID, 42, 8, @on
exec sp_trace_setevent @TraceID, 42, 10, @on
exec sp_trace_setevent @TraceID, 42, 11, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 42, 14, @on
exec sp_trace_setevent @TraceID, 42, 23, @on
exec sp_trace_setevent @TraceID, 42, 26, @on
exec sp_trace_setevent @TraceID, 72, 1, @on
exec sp_trace_setevent @TraceID, 72, 3, @on
exec sp_trace_setevent @TraceID, 72, 8, @on
exec sp_trace_setevent @TraceID, 72, 10, @on
exec sp_trace_setevent @TraceID, 72, 11, @on
exec sp_trace_setevent @TraceID, 72, 12, @on
exec sp_trace_setevent @TraceID, 72, 14, @on
exec sp_trace_setevent @TraceID, 72, 23, @on
exec sp_trace_setevent @TraceID, 72, 26, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'-- network protocol:%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'SET%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'DBCC%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'USE%'
-- Following two filters added 5/15/06
-- @P1=NULL identifies repetitive sp_prepexec fetches.
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'exec sp_cursorfetch %'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%set @P1=NULL%exec sp_prepexec @P1%'
--trace only specified databases
set @intfilter = (select DB_ID('PRODDWSQL'))
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter
set @intfilter = (select DB_ID('STAGEDWSQL'))
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQLAgent%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'%operating system%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Query%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'MS SQLEM'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
Greg
July 10, 2008 at 1:31 am
thanks greg and george for your help.
george,
i was just having a word with my VMWare administrator and he was telling that he will be configuring the base hardware the same way the physical m/c is. eg if the physical m/c is on raid 1 the server on vmware will also have raid 1.if the server is using 3gb ram then server on vmware will also have 3gb ram.
at present we have 15 physical m/c's so now these will be act as 15 different servers on vmware. so in short the underlying hardware would be the same.
we have all the dbs here configured on raid 1.
the vmware which they are buying now are really high end m/c's so there will be hardly any performance issues.(this is what he claims)
i do not knwo whther this move to vmware is good or not since i am new to this topic...but all i can say is GOD SAVE ME 🙂
July 10, 2008 at 3:34 am
well, looks like a done deal, so good luck. If you get performance problems make sure vmware admin is involved as well and its not just dumped on the 'database'. get yourself a performance baseline on the current server,
sql server total and target memory, available memory cpu utilisaton,
physical disk, read and write queue lengths, read and write \sec and sec\read and write
---------------------------------------------------------------------
July 10, 2008 at 4:57 am
greg,
i was just trying your script. a few doubts
1.should i configure the job on the same server on which i need to capture the trace ? what if the trace file gets full?
2. since the stoptime = null it will continue to run. incase i need to stop it do i need to shut down the mssqlserver?
3.is a seperate trace file created everyday?
i am getting error no 12 when i just run this script on my test server in the master db
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply