· 8 years ago · Apr 03, 2018, 05:30 AM
1/*
2Description: A record of what what VMs we want located where.
3Create sample lookup/static tables that defines a grouping of what App and SQL VMs
4Should be kept on the same ESXHost and/or the same site.
5It would be unusual to seperate related servers across sites but possible.
6Note that we are specifying which ESXHost the VM should be on but
7we are specifying which cluster and which primary site.
8*/
9
10USE Test1
11go
12if exists (select 1 from sysobjects where name = 'host_vm_grouping' and type = 'U') DROP TABLE host_vm_grouping
13GO
14CREATE TABLE host_vm_grouping
15(
16ID int IDENTITY (1,1),
17Cluster varchar(16), -- what cluster this VM is related to via the ESXHost
18--PrimarySite tinyint, -- which site the VM be in by default
19PreferedHost varchar(55),
20VM varchar(55), -- the name of the Virtual Machine
21VM_Type varchar(55), -- the role, eg. a SQL Server or Application server
22AppGroup varchar(55), -- the name of an Application or Service related to one or more VMs
23--SameESXHost binary, -- a flag to indicate if the VM should be on the same ESXHost or not
24--SameSite binary, -- should this VM should be in the same site as the other related VMs
25PerformanceRequirement char(1) -- H, M or L
26)
27GO
28-- App1: VMs to be grouped together on the same host, in site 1 by default
29INSERT INTO host_vm_grouping VALUES('C1',1,'VM1','APP','App1',1,1,'M')
30INSERT INTO host_vm_grouping VALUES('C1',1,'VM2','APP','App1',1,1,'M')
31INSERT INTO host_vm_grouping VALUES('C1',1,'VM3','SQL','App1',1,1,'M')
32
33-- App2: VMs don't have to be on the same host but it's ok if they are. Default site 2
34INSERT INTO host_vm_grouping VALUES('C2',2,'VM4','APP','App2',0,1,'L')
35INSERT INTO host_vm_grouping VALUES('C2',2,'VM5','SQL','App2',0,1,'L')
36
37-- App3: a mixed role VM both APP and SQL. Any host is ok in site 1.
38-- the values for 'SameHost' and 'SameSite' could be NULL as only one VM for this App group.
39INSERT INTO host_vm_grouping VALUES('C1',1,'VM7','Mixed','App3',0,0,'M')
40
41-- App4: VMs to be grouped together on the same host with in site 1 by default
42-- in this example, two of the app server VMs can be on any host
43INSERT INTO host_vm_grouping VALUES('C1',1,'VM8','APP','App4',0,1,'M')
44INSERT INTO host_vm_grouping VALUES('C1',1,'VM9','APP','App4',0,1,'M')
45INSERT INTO host_vm_grouping VALUES('C1',1,'VM10','APP','App4',1,1,'H')
46INSERT INTO host_vm_grouping VALUES('C1',1,'VM11','APP','App4',1,1,'H')
47INSERT INTO host_vm_grouping VALUES('C1',1,'VM12','SQL','App4',1,1,'H')
48go
49
50if exists (select 1 from sysobjects where name = 'HostRole' and type = 'U') DROP TABLE HostRole
51GO
52
53CREATE TABLE HostRole
54(
55ID TINYINT IDENTITY(1,1),
56HostRole VARCHAR(16) -- Mixed, SQL, App, Infra
57)
58GO
59
60INSERT INTO HostRole VALUES ('SQL')
61INSERT INTO HostRole VALUES ('App')
62INSERT INTO HostRole VALUES ('Mixed')
63INSERT INTO HostRole VALUES ('Infra')
64
65GO
66
67if exists (select 1 from sysobjects where name = 'ESXHost' and type = 'U') DROP TABLE ESXHost
68GO
69
70CREATE TABLE ESXHost
71(
72ESXHostID SMALLINT IDENTITY (1,1),
73ESXHostName VARCHAR(55),
74Cluster VARCHAR(16),
75Site TINYINT,
76PerformanceProfile CHAR(1),
77HostRoleID TINYINT
78)
79GO
80
81INSERT INTO ESXHost VALUES ('H1','C1',1,'H',1)
82INSERT INTO ESXHost VALUES ('H2','C1',2,'M',1)
83INSERT INTO ESXHost VALUES ('H3','C1',1,'L',2)
84INSERT INTO ESXHost VALUES ('H4','C1',2,'H',2)
85INSERT INTO ESXHost VALUES ('H5','C1',1,'L',2)
86INSERT INTO ESXHost VALUES ('H6','C1',2,'L',2)
87INSERT INTO ESXHost VALUES ('H7','C1',1,'L',2)
88INSERT INTO ESXHost VALUES ('H8','C1',2,'L',2)
89INSERT INTO ESXHost VALUES ('H9','C1',1,'L',2)
90INSERT INTO ESXHost VALUES ('H10','C1',2,'L',2)
91INSERT INTO ESXHost VALUES ('H11','C2',1,'L',2)
92INSERT INTO ESXHost VALUES ('H12','C2',2,'L',2)
93
94GO
95
96if exists (select 1 from sysobjects where name = 'Get_VMInfo' and type = 'U') DROP TABLE Get_VMInfo
97GO
98-- simulated data for current VM host location to allow queries to be developed
99CREATE TABLE Get_VMInfo
100(
101Cluster VARCHAR(16),
102ESXHostName VARCHAR(55),
103Site TINYINT, -- Would be derived from the VM name S1vmname = 'Site 1' S2vname = 'Site 2'
104VM VARCHAR(55)
105)
106GO
107-- Alter the data inserts to generate different test data sets
108
109-- in this case, VM3 should be on the same host as VM1 and VM2 but it isn't
110INSERT INTO Get_VMInfo VALUES('C1','H1',1,'VM1')
111INSERT INTO Get_VMInfo VALUES('C1','H1',1,'VM2')
112INSERT INTO Get_VMInfo VALUES('C1','H2',1,'VM3')
113
114-- in this case, the two VMs are where they should be
115INSERT INTO Get_VMInfo VALUES('C1','H2',1,'VM4')
116INSERT INTO Get_VMInfo VALUES('C1','H2',1,'VM5')
117
118-- In this case, just one VM to be concerned with and the only constraint is that
119-- the VM needs to have a primary site = 1 and Performanc Profile 'M' (host_vm_grouping)
120INSERT INTO Get_VMInfo VALUES('C1','H3',1,'VM7')
121
122
123-- in this case, we have a larger number of VMs with different rules to test
124-- whereby 3 of the 5 servers need to located on the same host but this doesn't apply
125-- to the other two VMs
126INSERT INTO Get_VMInfo VALUES('C1','H1',1,'VM8')
127INSERT INTO Get_VMInfo VALUES('C1','H1',1,'VM9')
128INSERT INTO Get_VMInfo VALUES('C1','H1',1,'VM10')
129INSERT INTO Get_VMInfo VALUES('C1','H1',1,'VM11')
130INSERT INTO Get_VMInfo VALUES('C1','H2',1,'VM12')
131
132
133/* */
134
135
136USE test1
137GO
138-- the host_vm_grouping table represents the rules for how VMs are located on Hosts
139-- given App groups and performance requirements, etc.
140select * from host_vm_grouping
141
142-- The ESXHost table represents static/lookup data related to config/performance profile
143-- of the ESX hosts. This table would be maintained manually.
144-- The purpose of this table to provide the base of info in terms of what ESX hoste exists,
145-- the architecture in terms of what Cluster and Site they relate to
146-- and the Peformance category - eg. the Host many have more processors, memory, etc
147-- We might want to look at how to use this to ensure only certain types of VMs go on
148-- specific hosts - eg. only SQL Server VMs on this host, etc
149
150select * from ESXHost
151
152SELECT
153 h.Cluster,
154 h.ESXHostName,
155 hr.HostRole,
156 h.PerformanceProfile,
157 h.Site
158FROM
159 ESXHost h
160 LEFT JOIN HostRole hr
161 ON h.ESXHostID = hr.ID
162
163
164-- Starting with the simplest query - are the VMS on the correct Cluster
165-- Note, it would be very rare that a VM was on the wrong cluster but a good case to start
166select
167 g.AppGroup,
168 g.VM,
169 g.VM_Type,
170 i.ESXHostName as 'actualESXHostName',
171 h.ESXHostName as 'targetESXHostName',
172 i.VM as 'actualVM',
173 g.PerformanceRequirement,
174 g.PrimarySite,
175 g.SameESXHost,
176 g.SameSite,
177 i.*,
178 h.*
179from
180 host_vm_grouping g -- grouping reqired but esxhost name not included
181 INNER JOIN Get_VMInfo i -- snapshot of current allocation of VMs ot Hosts
182 ON g.VM = i.VM
183 INNER JOIN ESXHost h -- static host info
184 ON i.ESXHostName = h.ESXHostName
185 LEFT JOIN HostRole hr
186 ON h.ESXHostID = hr.ID
187where
188 g.AppGroup = 'App4'
189
190
191
192select * from host_vm_grouping
193select * from Get_VMInfo
194select * from ESXHost
195
196
197create table #test
198(
199vm varchar(4),
200AppGroup varchar(8),
201SameESXHost tinyint,
202ActualHost char(2)
203)
204GO
205
206;with cte_VMMon
207AS
208(
209select
210 g.Cluster as 'DesiredCluster',
211 i.Cluster as 'ActualCluster',
212 CASE
213 WHEN g.Cluster <> i.Cluster THEN 'Wrong'
214 ELSE 'Right'
215 END as 'ClusterCheck',
216 g.PrimarySite as 'DesiredSite',
217 i.Site as 'ActualSite',
218 CASE
219 WHEN g.SameSite = 1 AND g.PrimarySite <> i.Site THEN 'Wrong'
220 ELSE 'Right'
221 END AS 'SiteCheck',
222 g.vm,
223 g.VM_Type,
224 g.AppGroup,
225 g.SameESXHost,
226 i.ESXHostName as 'ActualHost',
227 g.SameSite,
228 row_number() over (partition by i.ESXHostName order by g.vm ) as siteRank2,
229 g.PerformanceRequirement
230from
231 host_vm_grouping g
232 INNER JOIN Get_VMInfo i
233 ON g.vm = i.vm
234where
235 g.AppGroup = 'App4'
236)
237select
238 'insert into #test values(' + quotename(vm,'''') + ',' + quotename(AppGroup,'''') + ',' + cast(cast(SameESXHost as int) as char(1)) + ',' + quotename(ActualHost,'''') + ')'
239 --vm,
240 --AppGroup,
241 --cast(SameESXHost as int) as SameESXHost,
242 --ActualHost
243 --SameSite
244from
245 cte_VMMon
246
247--SELECT
248-- max(Actualhost) as Actualhost,
249-- max(siteRank2) as count
250--FROM
251-- cte_VMMon
252--group BY
253 ActualHost
254
255
256insert into #test values('VM8','App4',0,'H1')
257insert into #test values('VM9','App4',0,'H1')
258insert into #test values('VM10','App4',1,'H1')
259insert into #test values('VM11','App4',1,'H1')
260insert into #test values('VM12','App4',1,'H2')
261
262select * from #test