0

We currently have SQL Server Dev, QA, and StagingUAT instances sitting on same VM. The production resides in its own server.

The issue is, if one database MDF or TransactionLog on Dev, suddently consumes too much space, it wipes out hard drive space from all other Devops instances. They all share the same drive. This is a single point of failure. I inherited this legacy system.

Currently The Dev, QA, and StagingUAT Database MDF have the same G:\ drive.

Dev Databases: on G:\Dev

QA Databases on G:\QA

Staging Databases on G:\Staging

Transaction Logs are on:

Dev Database: on H:\Dev

QA Databases on H:\QA

Staging Databases on H:\Staging

What is the best way to prevent issue of one instance suddenly takes too much space?

Method 1: I recommended each Devops Instance having its own drive.

MDF Database:

Dev Database: on A:\Dev

QA Databases on B:\QA

Staging Databases on D:\Staging (skip C:)

Transaction Log:

Dev Database: on E:\Dev

QA Databases on F:\QA

Staging Databases on G:\Staging

Method 2:

Have each subfolder have a max hard drive space it can consume. G:\Dev can only take 10 GB max.

G:\QA can only take 10 GB max.

G:\Staging can only take 10 GB max.

We have current rule, databases on the same Devops instance should be on same drive. Currently we can only provision 1 VM for these devops instances.

Just curious best way to resolve this issue. When creating Devops instances,should each instance be separated from other instances?

  • 2
    Have you considered splitting this machine to have one instance per VM? This provides additional sandboxing for memory and CPU in addition to disk space. Non-Production SQL Server instances can run under Developer Edition, which is free in recent versions. – AMtwo Jun 12 '18 at 0:11
3

For each of the databases you can set a max size per file. Post about setting grow and file size

Here is Microsoft’s details on the alter file command

The advantages of doing it this way is that you can set each database to have a different file size and you don’t have to go to the trouble of creating the new drives and moving each database.

You can do this through click ops in the files section of each database setting the max file size for each file.

Alternatively you can script out the statements by doing a select on the sys.files table with some string building.

declare @usage table(Fileid int, FileGroup int, TotalExtents float, UsedExtents float, Name sysname, FileName nchar(520))
insert  @usage exec('dbcc showfilestats');
select [name], ((totalextents * cast(64 as float))/1024) [total]
,((usedextents * cast(64 as float))/1024) [used]
,((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024) [free]
, ((totalextents * cast(64 as float))/1024) - (((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024)) + ( (((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024)) * .3  ) [reduce]
,(1-(((usedextents * cast(64 as float))/1024) / ((totalextents * cast(64 as float))/1024))) [free %]
, 'alter database ' + DB_NAME() + ' modify file (name=' + rtrim([name]) + ', MAXSIZE = 50MB);' [Set Max File]
,'DBCC shrinkfile ([' + [name] + '],' + 
    cast(
        cast((((usedextents * cast(64 as float))/1024) + ( (((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024)) * .3  ))
        as int)
     as varchar)
+ ');' [shrink]
, len(substring([name], 1, 36))
--, (select gender + ' ' + sport from v_sportseason where id = substring([name], 1, 36))
, [filename]
, 'alter database ' + DB_NAME() + ' modify file (name=' + rtrim([name]) + ', filename=''' + rtrim([filename]) +''');' [Move File]
 from @usage
  • My answer is already tailored for your second option. There is no way to limit a folder in windows that I’m aware of unless you are talking about disk quotas and in that case you would have to have different instances of sql running as different users to enforce that. Can explain why simply setting the max size on the data and log files is not an option? You seem to be trying to make more work then what you need to limit the sizes. – Aaron Jun 16 '18 at 18:25
1

From a performance / noninterference view, separate drives are clearly the way to go. Even if you have to have the logs on the same drives as the databases, you are still better off. Of course, if it is one drive virtualized into multiple drives then you still have the noninterference piece covered at least.

The other consideration is, of course, hardware. If your hardware costs for dev run too high, you will be frowned at... or more. I assume that you have the possibility of throwing a bit more hardware at the question, so my recommendation is to do that.

  • well, I think my question is: Should each Devops instance atleast reside on separate hard drives? This is more of an environment devops rule, my original question is too long, just have just asked that, – user129291 Jun 12 '18 at 4:57
0

There is a wise saying "Do not put all your eggs in one basket".

When creating Devops instances, should each instance be separated from other instances?

Use virtualization (VMWare / HyperV, etc) and separate out your environments.

In both the solutions you are just looking at one aspect - DISK. What if your entire server goes down - blue screen, etc ?

DEV is production for your developers. You get new code developed from here. If this is not stable, your entire development cycle is disturbed.

QA is production for your testers. They find any bugs from the code that is going to get released upstream and finally make it to PROD.

Staging is a production resembled environment so that evaluation of code can be tested before being placed into production. Ideally staging is a mirror copy of PROD interms of hardware, processes, settings, etc.

UAT is where the users will be testing your code / upgrades, etc before it makes it to PROD on a daily basis. So technically, for those users, UAT is their PROD.

What is the best solution on Devops Perspective?

Use virtual machines or docker containers. (Not recommending K8s since persistent storage is still a question for K8s.) This way you can separate and then link all your devops pipeline thereby efficiently testing all your code deployments in a continuous way preferably using Jenkins or another tool of your choice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy